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.
COUNT() is one of the most used aggregate functions in MySQL. Other aggregate functions are SUM(), MIN(), MAX(), AVG(), etc.
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:
COUNT(expression)
Specify the column name to get the number of rows in the argument.
COUNT() Parameter
- 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_id | item_name | expired_date | sales_price |
|---|---|---|---|
| #ITEM_200001 | Fresh Milk | July 22nd 2019 | $2.00 |
| #ITEM_200002 | Orange Juice | August 10th 2019 | $1.50 |
| #ITEM_200003 | iPhone 6 | $300.00 | |
| #ITEM_200004 | Dell PC Desktop | $560.80 | |
| #ITEM_200005 | Pineapple Juice | May 4th 2019 | $1.20 |
| #ITEM_200006 | iPhone Case | $1.00 | |
| #ITEM_200007 | Galaxy Note | $240.00 | |
| #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.
Result:
8
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;
Result:
8
Summary
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.