SQL COUNT DISTINCT: Count Unique Values, Find Dupes
· 6 min read
To count distinct values in SQL, use COUNT(DISTINCT column), usually just called SQL COUNT DISTINCT. It counts how many different non-NULL values a column has. Repeats are counted once, and NULLs are not counted at all. Use it when the question says "how many unique", "how many different" or "how many customers" on a table where a customer can appear many times.
SELECT COUNT(DISTINCT customer_id) AS unique_customers
FROM orders;The rest of this guide shows count vs count distinct on the same data, how to count distinct values in a column per group, how to find duplicates, and the join mistake that inflates counts.
A worked example
| order_id | customer_id | channel |
|---|---|---|
| 1 | 10 | Online |
| 2 | 11 | Online |
| 3 | 10 | Phone |
| 4 | 12 | Online |
| 5 | 10 | Online |
| 6 | NULL | Phone |
Order 6 was a guest checkout, so it has no customer. The query above returns:
| unique_customers |
|---|
| 3 |
Customer 10 placed three orders but counts once. The NULL on order 6 is skipped. That leaves 10, 11 and 12.
COUNT vs COUNT DISTINCT
There are three kinds of count, and on this table they give three different answers:
SELECT COUNT(*) AS all_rows,
COUNT(customer_id) AS rows_with_customer,
COUNT(DISTINCT customer_id) AS unique_customers
FROM orders;| all_rows | rows_with_customer | unique_customers |
|---|---|---|
| 6 | 5 | 3 |
| Expression | Counts | NULLs |
|---|---|---|
COUNT(*) | every row | counted |
COUNT(column) | rows where the column has a value | skipped |
COUNT(DISTINCT column) | different values in the column | skipped |
So "how many orders" is COUNT(*) (6). "How many orders came from a known customer" is COUNT(customer_id) (5). "How many customers ordered" is COUNT(DISTINCT customer_id) (3). Picking the wrong one is the most common counting error in SQL.
Count distinct values in a column per group
Combine COUNT(DISTINCT ...) with GROUP BY to count unique values inside each group:
SELECT channel,
COUNT(*) AS orders,
COUNT(DISTINCT customer_id) AS unique_customers
FROM orders
GROUP BY channel
ORDER BY channel;| channel | orders | unique_customers |
|---|---|---|
| Online | 4 | 3 |
| Phone | 2 | 1 |
Online had four orders from three different customers. Notice that the group numbers do not add up to the overall count: Online 3 plus Phone 1 is 4, but there are only 3 unique customers. Customer 10 used both channels, so they are counted once in each group. Distinct counts are never safe to sum across groups.
Listing the distinct values
To see the values rather than count them, use SELECT DISTINCT:
SELECT DISTINCT customer_id
FROM orders
ORDER BY customer_id;| customer_id |
|---|
| 10 |
| 11 |
| 12 |
| NULL |
Unlike COUNT(DISTINCT ...), SELECT DISTINCT keeps NULL as one of the values.
Count distinct on multiple columns
To count unique combinations, such as customer and channel pairs, wrap a SELECT DISTINCT in a subquery. This works in every database:
SELECT COUNT(*) AS customer_channel_pairs
FROM (
SELECT DISTINCT customer_id, channel
FROM orders
) AS pairs;| customer_channel_pairs |
|---|
| 5 |
The five pairs are 10/Online, 11/Online, 10/Phone, 12/Online and NULL/Phone. The NULL pair is included here, unlike in COUNT(DISTINCT customer_id). Add WHERE customer_id IS NOT NULL inside the subquery and the answer becomes 4. MySQL also accepts COUNT(DISTINCT customer_id, channel), which skips any pair containing a NULL.
How to find duplicates
Counting distinct values tells you duplicates exist. To find out which values are duplicated, group by the column and keep groups with more than one row.
| contact_id | |
|---|---|
| 1 | ana@acme.test |
| 2 | ben@birch.test |
| 3 | ana@acme.test |
| 4 | cy@cobalt.test |
| 5 | ben@birch.test |
| 6 | ana@acme.test |
SELECT email, COUNT(*) AS times_found
FROM contacts
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY times_found DESC, email;| times_found | |
|---|---|
| ana@acme.test | 3 |
| ben@birch.test | 2 |
HAVING filters the groups after counting. If that part is new, see HAVING vs WHERE.
Show every duplicate row
To see the full rows behind each duplicate, feed that list back in with IN:
SELECT contact_id, email
FROM contacts
WHERE email IN (
SELECT email FROM contacts GROUP BY email HAVING COUNT(*) > 1
)
ORDER BY email, contact_id;| contact_id | |
|---|---|
| 1 | ana@acme.test |
| 3 | ana@acme.test |
| 6 | ana@acme.test |
| 2 | ben@birch.test |
| 5 | ben@birch.test |
Keep one row per duplicate
To keep only the first row for each email, number the rows within each email and keep number 1:
SELECT contact_id, email
FROM (
SELECT contact_id, email,
ROW_NUMBER() OVER (PARTITION BY email ORDER BY contact_id) AS rn
FROM contacts
) AS t
WHERE rn = 1
ORDER BY contact_id;| contact_id | |
|---|---|
| 1 | ana@acme.test |
| 2 | ben@birch.test |
| 4 | cy@cobalt.test |
ROW_NUMBER and PARTITION BY are covered in RANK OVER PARTITION BY.
Common mistakes
Counting rows after a join (the AI mistake to watch for)
Each order can have several product lines, stored in another table:
| order_id | product | quantity |
|---|---|---|
| 1 | Copy paper | 5 |
| 1 | Pens | 10 |
| 2 | Toner | 1 |
| 3 | Coffee beans | 2 |
| 3 | Paper cups | 50 |
| 3 | Tea | 4 |
| 4 | Mop | 1 |
| 5 | Copy paper | 8 |
| 5 | Stapler | 1 |
| 6 | Floor cleaner | 3 |
Ask an AI assistant for "orders per channel" in a query that also needs order_items, and it will often join and use COUNT(*):
SELECT o.channel, COUNT(*) AS orders
FROM orders AS o
JOIN order_items AS i ON i.order_id = o.order_id
GROUP BY o.channel
ORDER BY o.channel;| channel | orders |
|---|---|
| Online | 6 |
| Phone | 4 |
That is the number of order lines, not orders. After the join, order 3 appears three times. The fix is to count each order once:
SELECT o.channel, COUNT(DISTINCT o.order_id) AS orders
FROM orders AS o
JOIN order_items AS i ON i.order_id = o.order_id
GROUP BY o.channel
ORDER BY o.channel;| channel | orders |
|---|---|
| Online | 4 |
| Phone | 2 |
How to spot it: compare with a count on the original table. orders has 6 rows, so "orders per channel" must add up to 6. If it adds up to 10, something multiplied the rows. The same thing happens with SUM: summing an order-level column after joining to lines double counts it.
Using DISTINCT to hide a bad join
If a join returns duplicate rows you did not expect, adding DISTINCT makes them disappear from the output but leaves the real problem (usually a missing join condition). Find out why the rows repeat first. See how to join two tables.
Forgetting that NULLs are skipped
COUNT(DISTINCT customer_id) silently ignores guest orders. If they matter, count them separately with COUNT(*) - COUNT(customer_id).
FAQ
What is the difference between COUNT(*) and COUNT(1)?
None in practice. Both count every row, and modern databases run them the same way.
Is COUNT DISTINCT slow?
It is heavier than COUNT(*) because the database must remember every value it has seen. On very large tables, warehouses like BigQuery and Snowflake offer APPROX_COUNT_DISTINCT for a fast estimate.
Does COUNT DISTINCT count NULL?
No. NULL is not counted as a value. SELECT DISTINCT does show it as one row.
How do I count distinct values in SQL Server or MySQL?
The same way: COUNT(DISTINCT column) is standard SQL and works in SQL Server, MySQL, Postgres, SQLite, Oracle and DuckDB.
Practise this on a realistic dataset
Solve it yourself on one company's data, with instant feedback that points at what's wrong without giving away the answer.
Try “Customers per channel” →