SQL COUNT DISTINCT: Count Unique Values (With Examples)

COUNT(DISTINCT column) returns the number of different values in a column, counting each value once and skipping NULLs. Use it to answer questions like how many customers ordered, not how many orders were placed. This guide has the syntax, eight examples with real output, and the multi-column trick that trips people up.

By Matthew Rathbone · November 6, 2026

COUNT(DISTINCT column) counts how many different non-NULL values a column holds. If a customer placed five orders, COUNT(customer) counts them five times, but COUNT(DISTINCT customer) counts that customer once. It is the standard way to count unique customers, users, products or days in SQL.

Jump to: syntax · sample data · 8 examples · multiple columns · common mistakes · FAQ

COUNT DISTINCT syntax

SELECT COUNT(DISTINCT column_name)
FROM table_name;

Put DISTINCT inside the parentheses of COUNT. The database collects the values in that column, drops duplicates and NULLs, then counts what is left. It works the same in PostgreSQL, MySQL, SQL Server, SQLite and Oracle.

Expression	What it counts
`COUNT(*)`	Every row, including rows with `NULL`s
`COUNT(column)`	Rows where the column is not `NULL`
`COUNT(DISTINCT column)`	Different non-`NULL` values in the column

New to counting? Start with how to count in SQL, then come back.

Set up the sample data

Every query below ran on SQLite 3.45 and the output is real. Paste this into any SQL client, such as Beekeeper Studio, to follow along. Notice that Ana, Ben and Dev order more than once, and Eli's order has no product (NULL).

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, product TEXT,
                     region TEXT, amount INTEGER, order_date TEXT);
INSERT INTO orders VALUES
  (1, 'Ana',   'Laptop',   'East', 1200, '2026-01-05'),
  (2, 'Ana',   'Mouse',    'East',   25, '2026-01-05'),
  (3, 'Ben',   'Laptop',   'West', 1200, '2026-01-20'),
  (4, 'Ben',   'Laptop',   'West', 1200, '2026-02-11'),
  (5, 'Chloe', 'Monitor',  'East',  300, '2026-02-14'),
  (6, 'Dev',   'Mouse',    'West',   25, '2026-03-15'),
  (7, 'Dev',   'Keyboard', 'West',   80, '2026-03-20'),
  (8, 'Eli',   NULL,       'East',   50, '2026-03-22'),
  (9, 'Ana',   'Monitor',  'East',  300, '2026-03-25');

8 COUNT DISTINCT examples

1. Count rows vs unique values

SELECT COUNT(*) AS total_orders,
       COUNT(DISTINCT customer) AS unique_customers
FROM orders;

total_orders | unique_customers
9            | 5

There are nine orders but only five different customers. This is the whole point of DISTINCT: it answers "how many people", not "how many rows".

2. COUNT DISTINCT ignores NULL

SELECT COUNT(product) AS products_filled,
       COUNT(DISTINCT product) AS unique_products
FROM orders;

products_filled | unique_products
8               | 4

Eli's order has a NULL product, so only eight rows are counted, and those eight contain four distinct products (Laptop, Mouse, Monitor, Keyboard). NULL is never counted as a value. If you need NULL to count as its own value, add 1 when any exist, or replace it first with COALESCE: COUNT(DISTINCT COALESCE(product, 'none')) returns 5.

3. COUNT DISTINCT with GROUP BY

Count unique customers in each region:

SELECT region,
       COUNT(*) AS orders,
       COUNT(DISTINCT customer) AS customers
FROM orders
GROUP BY region;

region | orders | customers
East   | 5      | 3
West   | 4      | 2

East has five orders from three customers (Ana, Chloe, Eli). The distinct count is calculated separately inside each group. See how to use GROUP BY if that clause is new.

4. Active customers per month

SELECT substr(order_date, 1, 7) AS month,
       COUNT(DISTINCT customer) AS active_customers
FROM orders
GROUP BY 1
ORDER BY 1;

month   | active_customers
2026-01 | 2
2026-02 | 2
2026-03 | 3

This "monthly active users" shape is the most common real-world use. Ana ordered twice in January but is counted once. In PostgreSQL use DATE_TRUNC('month', order_date) and in MySQL DATE_FORMAT(order_date, '%Y-%m') instead of substr.

5. Find groups with more than one distinct value

Which customers bought more than one kind of product? Use COUNT(DISTINCT ...) in HAVING:

SELECT customer, COUNT(DISTINCT product) AS products
FROM orders
GROUP BY customer
HAVING COUNT(DISTINCT product) > 1;

customer | products
Ana      | 3
Dev      | 2

Ben ordered the laptop twice but only one distinct product, so he is excluded. Learn more in the HAVING clause guide.

6. Conditional distinct count

Wrap a CASE expression inside COUNT(DISTINCT ...) to count unique values that meet a condition. CASE returns NULL otherwise, and NULL is skipped:

SELECT COUNT(DISTINCT CASE WHEN amount >= 300 THEN customer END) AS big_spenders
FROM orders;

big_spenders
3

Ana, Ben and Chloe each have at least one order of 300 or more. Ana has several qualifying orders, yet counts once. See how to use CASE.

7. Several distinct counts in one query

SELECT COUNT(DISTINCT customer) AS customers,
       COUNT(DISTINCT product)  AS products,
       COUNT(DISTINCT region)   AS regions
FROM orders;

customers | products | regions
5         | 4        | 2

8. Rank groups by distinct count

SELECT customer, COUNT(DISTINCT product) AS products
FROM orders
GROUP BY customer
ORDER BY products DESC, customer;

customer | products
Ana      | 3
Dev      | 2
Ben      | 1
Chloe    | 1
Eli      | 0

Eli shows 0 because his only product is NULL, and COUNT returns 0 (not NULL) when there is nothing to count.

COUNT DISTINCT on multiple columns

People often search for "sql count distinct multiple columns", but COUNT(DISTINCT a, b) only works in MySQL. In other databases it is a syntax error. Here are the portable options, counting unique customer and product pairs:

Option 1: a subquery (works everywhere).

SELECT COUNT(*) AS unique_pairs
FROM (SELECT DISTINCT customer, product FROM orders) AS t;

unique_pairs
8

There are nine rows, and Ben's two identical Laptop orders collapse into one pair, giving eight.

Option 2: concatenate the columns.

SELECT COUNT(DISTINCT customer || '|' || product) AS unique_pairs
FROM orders;

unique_pairs
7

This returns 7, not 8, because Eli's pair contains a NULL, and joining anything with NULL gives NULL, which is skipped. Use COALESCE(product, '') if you want those rows, and always include a separator so that ('ab','c') and ('a','bc') stay different. In SQL Server use CONCAT(customer, '|', product) or +; in PostgreSQL and SQLite || works.

Option 3: a row constructor (PostgreSQL). COUNT(DISTINCT (customer, product)) works in PostgreSQL, though rows with a NULL inside are handled differently, so check your data.

The subquery in option 1 is the safest and the easiest to read. For readable multi-step versions, see SQL CTEs.

COUNT DISTINCT vs SELECT DISTINCT

SELECT DISTINCT customer FROM orders returns the list of unique values (five rows). SELECT COUNT(DISTINCT customer) FROM orders returns one number (5). Use the first to see the values and the second to count them. More in how to use SELECT DISTINCT.

Common mistakes

Using COUNT(*) when you mean unique. COUNT(*) returned 9 where the answer was 5 customers.
Expecting NULL to be counted. COUNT(DISTINCT col) skips NULL. Use COALESCE if NULL matters.
Writing COUNT(DISTINCT a, b) outside MySQL. Use the subquery pattern above.
Using it as a window function. In my SQLite test, COUNT(DISTINCT customer) OVER () failed with DISTINCT is not supported for window functions. SQL Server also rejects it. Calculate the distinct count in a CTE or subquery and join it back. See window functions for the alternatives.
Adding up distinct counts across groups. Summing monthly unique customers over-counts anyone active in two months. Calculate the distinct count at the level you want to report.
Ignoring performance. Distinct counts must sort or hash all values, so they are slower than COUNT(*) on huge tables. An index on the column can help, and some warehouses offer approximate versions (such as APPROX_COUNT_DISTINCT) for speed.

FAQ

What does COUNT(DISTINCT) do in SQL?

It counts the number of different non-NULL values in a column, counting each repeated value only once.

Does COUNT DISTINCT count NULL?

No. NULLs are ignored. If you want one extra count for NULL, replace it with a placeholder using COALESCE before counting.

How do I count distinct values across multiple columns?

Count the rows of a SELECT DISTINCT a, b subquery, or concatenate the columns inside COUNT(DISTINCT ...). Only MySQL accepts COUNT(DISTINCT a, b) directly.

Can I use COUNT DISTINCT with GROUP BY?

Yes. The distinct count is computed separately for each group, such as unique customers per region or per month.

What is the difference between COUNT(*) and COUNT(DISTINCT column)?

COUNT(*) counts all rows. COUNT(DISTINCT column) counts unique non-NULL values in that column, so it is equal to or smaller.

Is COUNT DISTINCT slow?

It is slower than a plain count because the database has to de-duplicate values. On small and medium tables you will not notice. On very large tables, add an index or use an approximate distinct count if your database offers one.

Practice counting

Try the free interactive GROUP BY lesson in your browser, then work through the SQL practice problems and more SQL query examples. Keep the SQL cheat sheet handy for syntax.

SQL COUNT DISTINCT: How to Count Unique Values