MySQL COUNT DISTINCT

VillageSQL is a drop-in replacement for MySQL with extensions.

All examples on this page work on VillageSQL. Install Now →

COUNT(*) answers how many rows. COUNT(DISTINCT column) answers how many different values, which is usually the more interesting question about a column you have not met. Open the client with mysql -u root -p sakila before you start.

How COUNT DISTINCT works

SELECT COUNT(DISTINCT COLUMN_NAME)
FROM TABLE_NAME;

Replace COLUMN_NAME with the column whose different values you want counted, and TABLE_NAME with the table. The server reads every row, throws away the repeats, and counts what is left. It is the count of what SELECT DISTINCT would return, without returning it.

Examples

1) How many different values

SELECT COUNT(*) AS rows_total, COUNT(DISTINCT rating) AS ratings FROM film;

+------------+---------+
| rows_total | ratings |
+------------+---------+
|       1000 |       5 |
+------------+---------+
1 row in set (0.01 sec)

1,000 films drawn from 5 ratings.

2) Different combinations

Name several columns and a combination counts once, the same way GROUP BY treats them.

SELECT COUNT(DISTINCT rating, rental_rate) AS combinations FROM film;

+--------------+
| combinations |
+--------------+
|           15 |
+--------------+
1 row in set (0.00 sec)

5 ratings times 3 rates, and every combination occurs. A row where any of the named columns is NULL is not counted at all.

It skips NULL, which can make the answer look wrong

address2 is NULL in 4 rows and an empty string in the other 599.

SELECT COUNT(*) AS rows_total,
       COUNT(DISTINCT address2) AS distinct_values
FROM address;

+------------+-----------------+
| rows_total | distinct_values |
+------------+-----------------+
|        603 |               1 |
+------------+-----------------+
1 row in set (0.00 sec)

The answer is 1. The empty string is 1 distinct value, and the NULLs are skipped rather than counted as a value of their own. So “how many different things are in this column” is not what the function answers when some rows are empty: it answers “how many different values”, and NULL is the absence of one. When you need both numbers, count the missing rows with a second query: SELECT COUNT(*) FROM address WHERE address2 IS NULL.

Summary

Use COUNT(DISTINCT column) to count different values rather than rows.
Name several columns to count distinct combinations.
Expect NULL to be skipped, not counted as a value.
Count the missing rows separately when you need both numbers.

Getting Started

Server Development

Using Extensions

Creating Extensions

VillageSQL is a drop-in replacement for MySQL with extensions.

How COUNT DISTINCT works

Examples

1) How many different values

2) Different combinations

It skips NULL, which can make the answer look wrong

Summary

See also

VillageSQL is a drop-in replacement for MySQL with extensions.

​How COUNT DISTINCT works

​Examples

​1) How many different values

​2) Different combinations

​It skips NULL, which can make the answer look wrong

​Summary

​See also

How COUNT DISTINCT works

Examples

1) How many different values

2) Different combinations

It skips NULL, which can make the answer look wrong

Summary

See also