How to Use the MySQL COUNT() Function with Examples
In this tutorial, you will learn to count the number of rows in a table using the MySQL COUNT function.
The COUNT() function is a common function used in SELECT statements.
MySQL COUNT() Syntax
The COUNT() function is used to get the number of non-NULL rows for the specified column as an argument.
The syntax of the COUNT() function is as follows:
Specify the column name to get the number of rows in the argument.
- The parameter expression is required and can be a column name or string value.
To get the number of rows from a specific table in a SELECT statement, write the syntax as follows:
SELECT COUNT(expression) FROM table;
You can use COUNT(*) as the argument to get the number of rows regardless of whether it is NULL or not.
SELECT COUNT(*) FROM table;
MySQL COUNT Function Examples
Suppose we have a table named items (see below) and consists of 4 columns: item_id, item_name, expired_date, and sales_price. Here we assume that the expired_date column can contain NULL values.
|#ITEM_200001||Fresh Milk||July 22nd 2019||$2.00|
|#ITEM_200002||Orange Juice||August 10th 2019||$1.50|
|#ITEM_200004||Dell PC Desktop||$560.80|
|#ITEM_200005||Pineapple Juice||May 4th 2019||$1.20|
|#ITEM_200008||Grapefruit Juice||June 5th 2019||$2.50|
COUNT Both NULL and Non-NULL Values
To count the number of rows that contain both NULL and non-NULL values in the items table, you can write the SQL query as follows:
SELECT COUNT(*) FROM items;
Here, the asterisk* is used as the COUNT() function parameter. So MySQL returns all the records in the items table.
COUNT with a Column that Contains NULL Values
This example shows how to use the COUNT() function on columns that contain NULL values. The expired_date column allows NULL values in this example.
Here is an example:
SELECT COUNT(expired_date) FROM items;
MySQL returns 4, which is the number of rows that are not NULL values.
Note that COUNT() doesn't count the NULL value.
COUNT with a Non-NULL Value Column
To count the number of rows that contain only non-NULL values, we often use COUNT(primary_key_column).
COUNT(primary_key_column) and COUNT(*) return the same result because the primary key column will never have a NULL value.
If item_id is a primary key column, the syntax for the COUNT() function is:
SELECT COUNT(item_id) FROM items;
In this tutorial, you learned how to use the COUNT() function in MySQL to count the number of rows in a particular table.
COUNT(*) counts the number of rows that contain both NULL and non-NULL values.
However, if you want to count the number of rows with non-NULL columns, you should use COUNT(primary_key_column) because there are no NULL values in the primary key column.