MariaDB COUNT() Function

In MariaDB, COUNT() is a built-in function that counts the number of non-NULL values among all values represented by an expression.

MariaDB `COUNT()` Syntax

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

COUNT(expr)

We typically use the COUNT() function:

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: The expr expression that needs to be counted. It can be a column name or an expression.
group_expr1, group_expr2, ...: The expression or column name used for grouping.

Return value

The MariaDB COUNT(expr) function returns the number of non-null values among all values represented by the expression in all rows returned by the SELECT statement.

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

MariaDB `COUNT()` Examples

We’ll demonstrate with the student_score table. Let’s first create a demo table and insert test rows.

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 all the rows 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 |
+----+------+---------+-------+

Basic example

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

SELECT COUNT(id) from student_score;

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

Let’s look again at 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, so the result of COUNT(score) is 5.

`GROUP BY`

Let’s use COUNT() and GROUP BY to calculate the average scores for more dimensions.

Count the number of scores for each subject

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

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

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

Count the number of scores for each student

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

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

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

Since Tim’s math score is NULL, so the reulst COUNT(score) of Tim is 1.

Conclusion

In MariaDB, COUNT() is a built-in function that counts the number of non-NULL values among all values represented by an expression.

MariaDB COUNT() Syntax