MySQL | COUNT method
Start your free 7-days trial now!
MySQL's COUNT(~) aggregate method counts the number of returned results.
Parameters
1. expr | expression
The expression to count the number of retrieved rows for.
Return value
The number of rows in expr that do not contain NULL values.
The exception is for COUNT(*) which counts retrieved rows including those containing NULL values.
Examples
Consider the following table about students' extracurricular activities:
student_id | club | date_entered |
|---|---|---|
1 | Football | 2016-02-13 |
2 | Boxing | 2016-05-25 |
3 | Apple | 2018-08-17 |
4 | Fishing | 2017-01-01 |
5 | NULL | NULL |
The above sample table can be created using the code here.
Basic usage
To count the number of clubs in extracurricular table:
SELECT COUNT(club) FROM extracurricular;
+-------------+| COUNT(club) |+-------------+| 4 |+-------------+
Note that the NULL value for student_id=5 is ignored, which is why we have a return value of 4.
COUNT(*)
To return the number of records in the extracurricular table:
SELECT COUNT(*) FROM extracurricular;
+----------+| COUNT(*) |+----------+| 5 |+----------+
Note that COUNT(*) returns a count of the number of rows retrieved, whether or not they contain NULL values.
Distinct records
To count the number of distinct clubs in extracurricular table:
SELECT COUNT(DISTINCT club) FROM extracurricular;
+----------------------+| COUNT(DISTINCT club) |+----------------------+| 4 |+----------------------+
Missing values
To count missing values, use the IS NULL operator like so:
SELECT COUNT(*) FROM extracurricular WHERE club IS NULL;
+----------+| COUNT(*) |+----------+| 1 |+----------+
We can see that there is one row in table extracurricular with a NULL club value.
Note that the following SQL does not work:
SELECT COUNT(*) FROM extracurricular WHERE club = NULL;
