MySQL COUNT() Function

The MySQL COUNT() function is used to count the number of non-null values ​​in all values ​​represented by an expression.

COUNT() Syntax

Here is the syntax of the MySQL COUNT() function:

COUNT(expr)

We usually use the COUNT() function like this:

SELECT COUNT(expr), ...
FROM table_name
[WHERE ...];

Or use the COUNT() function with the GROUP BY clause:

SELECT COUNT(expr), group_expr1, group_expr2, ...
FROM table_name
[WHERE ...]
GROUP BY group_expr1, group_expr2, ...;

Parameters

expr

An expression used for aggregate operations. It can be a column name or an expression.

group_expr1, group_expr2, ...

An expression or column name to use for grouping.

Return value

The COUNT(expr) function returns NULL the number of non-values ​​among all values ​​represented by expressions in all rows returned by the SELECT statement.

Note: If you use COUNT(*) or COUNT(1), the NULL value will be counted.

COUNT() Examples

We’ll demonstrate this function with a table named student_score. Let’s create the table and insert some rows firstly.

DROP TABLE IF EXISTS `student_score`;
CREATE TABLE `student_score` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL,
    `subject` VARCHAR(255) NOT NULL,
    `score` INT
);
INSERT INTO `student_score` (`name`, `subject`, `score`)
VALUES ('Tom', 'Math', 80),
    ('Tom', 'English', 90),
    ('Jim', 'Math', 84),
    ('Jim', 'English', 96),
    ('Tim', 'Math', NULL),
    ('Tim', 'English', 98);

Here is the data in the table:

+----+------+---------+-------+
| id | name | subject | score |
+----+------+---------+-------+
|  1 | Tom  | Math    |    80 |
|  2 | Tom  | English |    90 |
|  3 | Jim  | Math    |    84 |
|  4 | Jim  | English |    96 |
|  5 | Tim  | Math    |  NULL |
|  6 | Tim  | English |    98 |
+----+------+---------+-------+

COUNT() Basic Example

The following SQL statement returns the total number of rows in the student_score table.

SELECT COUNT(id) from student_score;

+-----------+
| COUNT(id) |
+-----------+
|         6 |
+-----------+

Let’s count the number of all scores in the student_score table .

SELECT COUNT(score) from student_score;

+--------------+
| COUNT(score) |
+--------------+
|            5 |
+--------------+

Here, since there is a NULL value in the score column, the result of COUNT(score) is 5.

COUNT() And GROUP BY Example

Let’s use COUNT() and GROUP BY to count quantities from more dimensions.

1. Count the number of grades by subject

   SELECT subject, COUNT(score)
   FROM student_score
   GROUP BY subject;
   

   +---------+-----------+
   | subject | COUNT(id) |
   +---------+-----------+
   | Math    |         2 |
   | English |         3 |
   +---------+-----------+

   Here, MySQL will first group the rows by subject according to GROUP BY subject, and then execute COUNT(score) within each group.

2. Count the number of grades by name

   SELECT name, COUNT(score)
   FROM student_score
   GROUP BY name;
   

   +------+--------------+
   | name | COUNT(score) |
   +------+--------------+
   | Tom  |            2 |
   | Jim  |            2 |
   | Tim  |            1 |
   +------+--------------+

   Here, MySQL will first name group the rows by name according to GROUP BY name, and then execute COUNT(score) within each group.

   Since Tim’s math score is NULL, the COUNT(score) for Tim is 1.