W3Schools is optimized for learning and training. Examples might be simplified to improve reading and learning. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. While using W3Schools, you agree to have read and accepted our terms of use, cookie and privacy policy.
Copyright 1999-2022 by Refsnes Data. All Rights Reserved.
W3Schools is Powered by W3.CSS.
MySQL Count[] Function
MySQL count[] function is used to returns the count of an expression. It allows us to count all rows or only some rows of the table that matches a specified condition. It is a type of aggregate function whose return type is BIGINT. This function returns 0 if it does not find any matching rows.
We can use the count function in three forms, which are explained below:
- Count [*]
- Count [expression]
- Count [distinct]
Let us discuss each in detail.
COUNT[*] Function: This function uses the SELECT statement to returns the count of rows in a result set. The result set contains all Non-Null, Null, and duplicates rows.
COUNT[expression] Function: This function returns the result set without containing Null rows as the result of an expression.
COUNT[distinct expression] Function: This function returns the count of distinct rows without containing NULL values as the result of the expression.
Syntax
The following are the syntax of the COUNT[] function:
Parameter explanation
aggregate_expression: It specifies the column or expression whose NON-NULL values will be counted.
table_name: It specifies the tables from where you want to retrieve records. There must be at least one table listed in the FROM clause.
WHERE conditions: It is optional. It specifies the conditions that must be fulfilled for the records to be selected.
MySQL count[] function example
Consider a table named "employees" that contains the following data.
Let us understand how count[] functions work in MySQL.
Example1
Execute the following query that uses the COUNT[expression] function to calculates the total number of employees name available in the table:
Output:
Example2
Execute the following statement that returns all rows from the employee table and WHERE clause specifies the rows whose value in the column emp_age is greater than 32:
Output:
Example3
This statement uses the COUNT[distinct expression] function that counts the Non-Null and distinct rows in the column emp_age:
Output:
MySQL Count[] Function with GROUP BY Clause
We can also use the count[] function with the GROUP BY clause that returns the count of the element in each group. For example, the following statement returns the number of employee in each city:
After the successful execution, we will get the result as below:
MySQL Count[] Function with HAVING and ORDER BY Clause
Let us see another clause that uses ORDER BY and Having clause with the count[] function. Execute the following statement that gives the employee name who has at least two age same and sorts them based on the count result:
This statement will give the output as below:
Summary: in this tutorial, you will learn how to use the MySQL COUNT[]
function to return the number rows in a table.
Introduction to the MySQL COUNT[]
function
The COUNT[]
function is an aggregate function that returns the number of rows in a table. The COUNT[]
function allows you to count all rows or only rows that match a
specified condition.
The COUNT[]
function has three forms: COUNT[*]
, COUNT[expression]
and COUNT[DISTINCT expression]
.
COUNT[*]
function
The COUNT[*]
function returns the number of rows in a result set returned by a SELECT
statement. The COUNT[*]
returns the number of rows including duplicate, non-NULL and NULL
rows.
COUNT[expression]
The COUNT[expression]
returns the number of rows that do not contain NULL
values as the result of the expression.
COUNT[DISTINCT expression]
The COUNT[DISTINCT expression]
returns the
number of distinct rows that do not contain NULL
values as the result of the expression.
The return type of the COUNT[]
function is BIGINT
. The COUNT[]
function returns 0 if there is no matching row found.
MySQL COUNT[]
function illustration
Setting up a sample table
First, create a table called count_demos
:
Code language: SQL [Structured Query Language] [sql]
CREATE TABLE count_demos [ id INT AUTO_INCREMENT PRIMARY KEY, val INT ];
Second,
insert some rows into the count_demos
table:
Code language: SQL [Structured Query Language] [sql]
INSERT INTO count_demos[val] VALUES[1],[1],[2],[2],[NULL],[3],[4],[NULL],[5];
Third, query data from the count_demos
table:
Code language: SQL [Structured Query Language] [sql]
SELECT * FROM count_demos;
MySQL COUNT[*]
example
The following statement uses the COUNT[*]
function to return all rows from the count_demos
table:
Code language: SQL [Structured Query Language] [sql]
SELECT COUNT[*] FROM count_demos;
This example uses the COUNT[*]
function with a WHERE
clause
to specify a condition to count only rows whose value in the column val
is 2:
Code language: SQL [Structured Query Language] [sql]
SELECT COUNT[*] FROM count_demos WHERE val = 2;
MySQL COUNT[expression]
example
If you specify the val
column in the COUNT[]
function, the
COUNT[]
function will count only rows with non-NULL values in the val
column:
Code language: SQL [Structured Query Language] [sql]
SELECT COUNT[val] FROM count_demos;
Notice that two NULL
values are not included in the result.
MySQL COUNT[DISTINCT expression]
example
This example uses COUNT[DISTINCT expression]
to count non-NULL and
distinct values in the column val
:
SELECT COUNT[DISTINCT val] FROM count_demos;
Code language: SQL [Structured Query Language] [sql]
MySQL COUNT[]
function practical examples
We’ll use the products
table from the
sample database for the next examples:
A] Using MySQL COUNT[*]
function with a GROUP BY
example
The COUNT[*]
function is often used with a GROUP BY
clause to return the number of elements in each group.
For example, this statement uses the COUNT[]
function with the
GROUP BY
clause to return the number of products in each product line:
Code language: SQL [Structured Query Language] [sql]
SELECT productLine, COUNT[*] FROM products GROUP BY productLine;
Similarly, this
example uses the COUNT[*]
function to find the number of products supplied by each vendor:
Code language: SQL [Structured Query Language] [sql]
SELECT productVendor, COUNT[*] FROM products GROUP BY productVendor ORDER BY COUNT[*] DESC;
B] Using MySQL COUNT[*]
with a HAVING
clause example
To find vendors who supply at least 9 products, you use the COUNT[*]
function in the HAVING
clause as shown in the following
query:
Code language: SQL [Structured Query Language] [sql]
SELECT productVendor, COUNT[*] FROM products GROUP BY productVendor HAVING COUNT[*] >= 9 ORDER BY COUNT[*] DESC;
C] MySQL COUNT IF
example
You can use a control flow expression and functions e.g., IF
, IFNULL
, and CASE
in the COUNT[]
function to count
rows whose values match a condition.
See the following orders
table from the sample database:
The following query use COUNT[]
with IF
function to find the number of canceled, on hold and disputed orders from the orders
table:
Code language: SQL [Structured Query Language] [sql]
SELECT COUNT[IF[status = 'Cancelled', 1, NULL]] 'Cancelled', COUNT[IF[status = 'On Hold', 1, NULL]] 'On Hold', COUNT[IF[status = 'Disputed', 1, NULL]] 'Disputed' FROM orders;
Try It Out
The IF[]
function returns 1 if the order’s status is canceled, on hold or disputed, otherwise, it returns NULL
.
The COUNT
function only counts 1, not NULL values, therefore, the query returns the number of orders based on the corresponding status.
In this tutorial, you have learned various techniques to count the number of rows in a table using the MySQL COUNT
function.
Was this tutorial helpful?