MySQL COUNT
Summary: in this tutorial, you will learn how to use the MySQL COUNT()
function to return the number of rows in a table.
Introduction to the MySQL COUNT()
function
The COUNT()
the 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()
the function has three forms: COUNT(*)
, COUNT(expression)
and COUNT(DISTINCT expression)
.
COUNT(*)
function
The COUNT(*)
the 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()
the 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
:
CREATE TABLE count_demos (
id INT AUTO_INCREMENT PRIMARY KEY,
val INT
);
Second, insert some rows into the count_demos
table:
INSERT INTO count_demos(val)
VALUES(1),(1),(2),(2),(NULL),(3),(4),(NULL),(5);
Third, query data from the count_demos
table:
SELECT * FROM count_demos;
MySQL COUNT(*)
example
The following statement uses the COUNT(*)
function to return all rows from the count_demos
table:
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:
SELECT COUNT(*)
FROM count_demos
WHERE val = 2;
MySQL COUNT(expression)
example
If you specify the val
column in the COUNT()
function, the COUNT()
the function will count only rows with non-NULL values in the val
column:
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;
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(*)
the 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:
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:
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:
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:
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;
The IF()
the 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.
0 Comments
CAN FEEDBACK
Emoji