MySQL COUNT() Function – Count the Number of Rows

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:


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.


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_200001Fresh MilkJuly 22nd 2019$2.00
#ITEM_200002Orange JuiceAugust 10th 2019$1.50
#ITEM_200003iPhone 6 $300.00
#ITEM_200004Dell PC Desktop $560.80
#ITEM_200005Pineapple JuiceMay 4th 2019$1.20
#ITEM_200006iPhone Case $1.00
#ITEM_200007Galaxy Note $240.00
#ITEM_200008Grapefruit JuiceJune 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:


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.

See also:
MySQL LIKE Operator Pattern Matching and Examples
MySQL SUBSTRING_INDEX Function with Examples
MySQL EXISTS Operator with Examples
MySQL ROW_NUMBER Function with Examples
MySQL CONCAT() Function | Concatenate Strings in MySQL

Leave a Comment