MySQL COUNT Function

Published: 15 December 2025
on channel: Learn With Passion

In this tutorial, you will learn how to use the MySQL COUNT() function to return the number of rows in a table.
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() function will count only rows with non-NULL values in the val column:

SELECT COUNT(val)
FROM count_demos;
Note that two NULL values are not counted.

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.
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:

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;

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(*) greater than= 9
ORDER BY COUNT(*) DESC;

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.
Use the orders table:
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() 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.

In this tutorial, you have learned various techniques to count the number of rows in a table using the MySQL COUNT function.

On this page of the site you can watch the video online MySQL COUNT Function with a duration of hours minute second in good quality, which was uploaded by the user Learn With Passion 15 December 2025, share the link with friends and acquaintances, this video has already been watched 14 times on youtube and it was liked by 2 viewers. Enjoy your viewing!

804