> ## Documentation Index
> Fetch the complete documentation index at: https://villagesql.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# MySQL COUNT DISTINCT

> How COUNT(DISTINCT column) counts different values rather than rows in MySQL, how to count distinct combinations, and why it skips NULL entirely.

<Card title="VillageSQL is a drop-in replacement for MySQL with extensions." icon="database" href="/docs/mysql-8.4/stable/quickstart">
  All examples on this page work on VillageSQL. Install Now →
</Card>

`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

```sql theme={null}
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](/docs/tutorial/select-distinct) would
return, without returning it.

## Examples

### 1) How many different values

```sql theme={null}
SELECT COUNT(*) AS rows_total, COUNT(DISTINCT rating) AS ratings FROM film;
```

```text theme={null}
+------------+---------+
| 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.

```sql theme={null}
SELECT COUNT(DISTINCT rating, rental_rate) AS combinations FROM film;
```

```text theme={null}
+--------------+
| 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.

```sql theme={null}
SELECT COUNT(*) AS rows_total,
       COUNT(DISTINCT address2) AS distinct_values
FROM address;
```

```text theme={null}
+------------+-----------------+
| 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.

## See also

* [Aggregate functions](/docs/tutorial/aggregate-functions) — the rest of the family
* [SELECT DISTINCT](/docs/tutorial/select-distinct) — returning those values instead of counting them
* [GROUP BY](/docs/tutorial/group-by) — counting per category
* [MySQL GROUP BY performance](/docs/guides/group-by-having) — what deduplicating costs at scale