How to Use the MySQL COUNT() Function
In this tutorial, you'll learn how to use the MySQL COUNT function to count the number of records in a table.
MySQL COUNT() is one of the aggregate functions in MySQL. It allows you to count the number of records in a table. The COUNT() function is a function that often used in the SELECT statement.
MySQL COUNT() Syntax
The syntax of the COUNT() function is as follows:
- The parameter expression is required and can be a column name or string value.
To use COUNT() in the SELECT statement, you can write as follows:
SELECT COUNT(expression) FROM table;
You can use COUNT(*) to count the number of records that contain both NULL and non-NULL values.
SELECT COUNT(*) FROM table;
MySQL COUNT Function Examples
Suppose you have the items table (see below) that consists of 4 columns, item_id, item_name, expired_date, and sales_price. Here, assume that the expired_date column can contain the NULL value.
|#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
Let's say you want to count the number of records that contains both NULL and non-NULL values. You can write the MySQL COUNT() function in the SELECT statement as follows:
SELECT COUNT(*) FROM items;
Here, the asterisk * used as the COUNT() function parameter. So, MySQL returns all records in the items table.
COUNT with a Column that Contains NULL Values
In this example, I show how to use the COUNT() function with a column that contains a NULL value. Since the expired_date column allows NULL value, let's take the expired_date column for this example.
Let's take a look at an example:
SELECT COUNT(expired_date) FROM items;
MySQL returns 4, which is the number of records 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 records that contains only non-NULL values, we often use COUNT(primary_key_column).
Note that COUNT(primary_key_column) and COUNT(*) return the same result because the primary key column will never have a NULL value.
Let's say item_id is a primary key column, then the syntax of the COUNT() function will look like this:
SELECT COUNT(item_id) FROM items;
In this tutorial, you've learned how to use the COUNT() function in MySQL to count the number of records in a given table.
If you need to count the number of records containing both NULL and non-NULL values, use the COUNT(*) function.
However, if you want to count the number of records with the non-NULL column, you should use COUNT(primary_key_column), because the primary key column will never have a NULL value.