In MariaDB, the COUNT() aggregate function returns a count of the number of non-NULL values of an expression in the rows retrieved by a SELECT statement.

Sample Data

Suppose we have the following table:

SELECT * 
FROM Pets;

Result:

+-------+-----------+---------+---------+------------+
| PetId | PetTypeId | OwnerId | PetName | DOB        |
+-------+-----------+---------+---------+------------+
|     1 |         2 |       3 | Fluffy  | 2020-11-20 |
|     2 |         3 |       3 | Fetch   | 2019-08-16 |
|     3 |         2 |       2 | Scratch | 2018-10-01 |
|     4 |         3 |       3 | Wag     | 2020-03-15 |
|     5 |         1 |       1 | Tweet   | 2020-11-28 |
|     6 |         3 |       4 | Fluffy  | 2020-09-17 |
|     7 |         3 |       2 | Bark    | NULL       |
|     8 |         2 |       4 | Meow    | NULL       |
+-------+-----------+---------+---------+------------+

The following examples will use the COUNT() function when querying this table.

Example

Here’s how to count the rows in the above table:

SELECT COUNT(*)
FROM Pets;

Result:

8

This tells us that there’s 8 rows in the table. We know that because we used the asterisk wildcard (*) to specify all rows and all columns.

Count a Specific Column

You can also specify a column to count. The COUNT() function only counts non-NULL results, so if you specify a column that contains NULL values, those values won’t be counted.

Here’s an example to demonstrate what I mean.

SELECT COUNT(DOB)
FROM Pets;

Result:

6

In this case, the Pets table contains two NULL values in the DOB column (two pets haven’t supplied their date of birth), and so COUNT(DOB) returns 6, instead of 8 when we used COUNT(*).

The reason COUNT(*) in the previous example returned all rows, is because those two rows did have data in the other columns.

Filtered Results

The COUNT() function counts the rows returned by the query. So if you filter the results, the result of COUNT() will reflect that.

SELECT COUNT(*)
FROM Pets
WHERE PetName = 'Fluffy';

Result:

2

In this case, there are two pets with a name of Fluffy. Therefore, two rows would have been returned by the query, and the result of COUNT() is 2.

Count Distinct Columns

We have the option of adding the DISTINCT keyword to specify that just distinct values are returned (so that it excludes duplicates):

SELECT 
    COUNT(PetName) AS "All",
    COUNT(DISTINCT PetName) AS "Distinct"
FROM Pets;

Result:

+-----+----------+
| All | Distinct |
+-----+----------+
|   8 |        7 |
+-----+----------+

In this case, even though there are eight PetName values, only seven of them are distinct (two are duplicates – we have two pets called Fluffy).

The HAVING Clause

You can include COUNT() in multiple parts of a query. It’s not just limited to the SELECT list.

Here’s an example that uses COUNT() in both the HAVING clause and the SELECT list:

SELECT 
    PetTypeId, 
    COUNT(PetTypeId) AS Count
FROM Pets
GROUP BY PetTypeId
HAVING COUNT(PetTypeId) > 2
ORDER BY Count DESC;

Result:

+-------------+---------+
| PetTypeId   | Count   |
|-------------+---------|
| 3           | 4       |
| 2           | 3       |
+-------------+---------+

See SQL COUNT() for Beginners for a more detailed explanation of this example, as well as an example of using COUNT() in a window function.