MySQL COUNT() – Get the Number of Rows to be Returned by a Query

MySQL includes a COUNT() function, which allows you to find out how many rows would be returned from a query. This function is part of the SQL standard, and it can be used with most relational database management systems.

The COUNT() function can also be used to affect the results of a query if required, for example, by only returning those results that have a row count greater than a given amount.

This article contains examples of COUNT() usage in MySQL.

The Data

First, here’s the data we’ll use for the examples on this page:

SELECT *
FROM Tasks;

Result:

+--------+-------------------+-----------------+
| TaskId | TaskName          | TaskDescription |
+--------+-------------------+-----------------+
|      1 | Do garden         | NULL            |
|      2 | Feed cats         | NULL            |
|      3 | Paint roof        | NULL            |
|      4 | Take dog for walk | NULL            |
|      5 | Relax             | NULL            |
|      6 | Feed cats         | NULL            |
+--------+-------------------+-----------------+

Count All Rows in a Table

You can use COUNT() to return the total number of rows in a table:

SELECT COUNT(*)
FROM Tasks;

Result:

+----------+
| COUNT(*) |
+----------+
|        6 |
+----------+

This returns the number of rows in the table because we didn’t provide any criteria to narrow the results down.

Narrow the Results

When we add a WHERE clause that narrows down the result set, we get a smaller number:

SELECT COUNT(*)
FROM Tasks
WHERE TaskName LIKE '%cat%';

Result:

+----------+
| COUNT(*) |
+----------+
|        2 |
+----------+

Single Column vs Asterisk (*)

The previous examples all use an asterisk to apply the count to all columns. As with any query, the asterisk is optional, and it is used as a wildcard to return all columns. So when using the COUNT() function, you also have the option to provide specific columns (as opposed to all columns) as the argument.

Example:

SELECT COUNT(TaskName)
FROM Tasks;

Result:

+-----------------+
| COUNT(TaskName) |
+-----------------+
|               6 |
+-----------------+

While this example returns the same result that we got when we used the asterisk, that isn’t necessarily always going to be the case. For example, here’s what happens if we specify a different column:

SELECT COUNT(TaskDescription)
FROM Tasks;

Result:

+------------------------+
| COUNT(TaskDescription) |
+------------------------+
|                      0 |
+------------------------+

In this case we get zero, because that particular column contains null values in every row.

Example – Distinct

You can add the DISTINCT argument to return only the number of rows with different non-NULL values.

You might’ve noticed that the TaskName column has a duplicate value (“Feed cats” appears twice). This could cause problems if you don’t want duplicates to be counted.

Here’s what it looks like if we apply the DISTINCT argument to the TaskName column:

SELECT COUNT(DISTINCT TaskName)
FROM Tasks;

Result:

+--------------------------+
| COUNT(DISTINCT TaskName) |
+--------------------------+
|                        5 |
+--------------------------+

So, although the table contains six rows, two of them are duplicates. Therefore, those two are counted as one we get a result of five.

Example – The HAVING Clause

You can also use COUNT() with the HAVING clause to limit a result set based on the number of rows that would be returned.

Here’s an example using a different data set to the previous examples:

USE Music;
SELECT ar.ArtistName,   
       COUNT(al.AlbumName) 'Album Count'
FROM Artists ar
INNER JOIN Albums al 
ON ar.ArtistId = al.ArtistId
GROUP BY ar.ArtistName
HAVING COUNT(al.AlbumName) > 1;

Result:

+------------------------+-------------+
| ArtistName             | Album Count |
+------------------------+-------------+
| Iron Maiden            |           5 |
| Devin Townsend         |           3 |
| Michael Learns to Rock |           3 |
| Tom Jones              |           3 |
| Allan Holdsworth       |           2 |
+------------------------+-------------+

This query returns only those artists who have released more than 1 album. Here’s what happens when we increase the value in the HAVING clause:

USE Music;
SELECT ar.ArtistName,   
       COUNT(al.AlbumName) 'Album Count'
FROM Artists ar
INNER JOIN Albums al 
ON ar.ArtistId = al.ArtistId
GROUP BY ar.ArtistName
HAVING COUNT(al.AlbumName) > 4;

Result:

+-------------+-------------+
| ArtistName  | Album Count |
+-------------+-------------+
| Iron Maiden |           5 |
+-------------+-------------+