MySQL COUNT() Function with Examples


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:

COUNT(expression)

COUNT() Parameter

  • 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_iditem_nameexpired_date, and sales_price. Here, assume that the expired_date column can contain the NULL value.

item_iditem_nameexpired_datesales_price
#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

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.

Result:

8

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;

Result:

8

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.

Leave a Reply

Your email address will not be published. Required fields are marked *