The MySQL COUNT() function allows you to count how many times a certain value appears in your MySQL database.
The function can also help you to count how many rows you have in your MySQL table.
The function has one expression parameter where you can specify the condition of the query.
The syntax of the COUNT() function is as follows:
SELECT COUNT(expression)
For example, suppose you have a pets table that lists the pets age, species, and owner as follows:
+----+--------+---------+------+
| id | owner | species | age |
+----+--------+---------+------+
| 1 | Jessie | bird | 2 |
| 2 | Jessie | dog | 6 |
| 3 | Ann | duck | 3 |
| 4 | Ann | duck | 2 |
| 5 | Ann | duck | 3 |
| 6 | Gary | cat | NULL |
| 7 | Gary | cat | 4 |
| 8 | Joe | horse | 4 |
| 9 | Mark | dog | 4 |
| 10 | Peter | dog | 5 |
+----+--------+---------+------+
Suppose you want to count how many rows the pets table has. You can use the asterisk (*) symbol as the argument to the COUNT() function.
The asterisk symbol in MySQL means select all columns in the table, so COUNT(*) expression means all rows will be counted, even those with null and duplicate values.
The following MySQL query:
SELECT COUNT(*) FROM pets;
Will produce the following result set:
+----------+
| COUNT(*) |
+----------+
| 10 |
+----------+
If you want to count only rows with a specific field value, then you can add the WHERE clause to the query.
For example, suppose you only want to count only rows with the age value of 4. You can use the following query:
SELECT COUNT(*) FROM pets WHERE age = 4;
The result set will be as follows:
+----------+
| COUNT(*) |
+----------+
| 3 |
+----------+
Since we have three pets that are 4 years old, the query works as intended. But the result can be more informative by also returning the species of the pets.
To return the species of the pets, you need to add the species column to the SELECT statement, then group the query by the species column using the GROUP BY clause.
The following query:
SELECT species, COUNT(*) FROM pets WHERE age = 4 GROUP BY species;
Will produce the following result set:
+---------+----------+
| species | COUNT(*) |
+---------+----------+
| cat | 1 |
| horse | 1 |
| dog | 1 |
+---------+----------+
Now you know that there are one cat, one horse, and one dog that are four years old.
The COUNT function is an aggregate function, which means that you need to use the GROUP BY clause when you also select non-aggregate fields (the normal select column statements)
By default, only a non null value is included in the result set.
In the following SQL query, the age column with null value is excluded from the result set:
SELECT species, COUNT(age) FROM pets GROUP BY species;
+---------+------------+
| species | COUNT(age) |
+---------+------------+
| bird | 1 |
| dog | 3 |
| duck | 3 |
| cat | 1 |
| horse | 1 |
+---------+------------+
While there are actually two rows of cat species, MySQL only counts one row because the other cat row has the age value of NULL.
Finally, you can also know how many animals each owner has in your table with the following query:
SELECT owner, COUNT(*) FROM pets GROUP BY owner;
The result will be as shown below:
+--------+----------+
| owner | COUNT(*) |
+--------+----------+
| Jessie | 2 |
| Ann | 3 |
| Gary | 2 |
| Joe | 1 |
| Mark | 1 |
| Peter | 1 |
+--------+----------+
Now you’ve learned how MySQL COUNT() function works. Nice work! 😉