SQL practice › SQL reference › COUNT(DISTINCT)
SQL COUNT(DISTINCT): count each value once, with queries you can run
COUNT(DISTINCT customer_id) counts how many different values the column holds: each value once, however many rows repeat it, and NULL not at all. COUNT(*) counts rows. COUNT(customer_id) counts the rows where the column isn't NULL. On the orders table below, the three give 10, 9 and 4.
It is how you get "how many customers" out of a table that has one row per order.
Syntax
SELECT COUNT(DISTINCT customer_id)
FROM ordersCOUNT(*)- The number of rows.
COUNT(customer_id)- The number of rows where
customer_idis not NULL. COUNT(DISTINCT customer_id)- The number of different non-NULL values of
customer_id.DISTINCTgoes inside the parentheses.
The two tables
Ten orders. Order 109 was placed by a guest, so its customer_id is NULL. Ben (customer 2) bought a lamp twice. Eve has never ordered.
| id | name | city |
|---|---|---|
| 1 | Ava | Denver |
| 2 | Ben | Austin |
| 3 | Chloe | Denver |
| 4 | Dan | Boston |
| 5 | Eve | Austin |
| id | customer_id | product | amount | order_date |
|---|---|---|---|---|
| 101 | 1 | Desk | 240 | 2026-01-05 |
| 102 | 2 | Lamp | 35 | 2026-01-09 |
| 103 | 1 | Chair | 120 | 2026-01-17 |
| 104 | 3 | Monitor | 180 | 2026-02-02 |
| 105 | 2 | Desk | 240 | 2026-02-11 |
| 106 | 4 | Lamp | 35 | 2026-02-20 |
| 107 | 1 | Lamp | 35 | 2026-03-03 |
| 108 | 3 | Chair | 120 | 2026-03-14 |
| 109 | NULL | Lamp | 35 | 2026-03-21 |
| 110 | 2 | Lamp | 35 | 2026-03-25 |
Table: orders
| id | customer_id | product | amount | order_date |
|---|---|---|---|---|
| 101 | 1 | Desk | 240 | 2026-01-05 |
| 102 | 2 | Lamp | 35 | 2026-01-09 |
| 103 | 1 | Chair | 120 | 2026-01-17 |
| 104 | 3 | Monitor | 180 | 2026-02-02 |
| 105 | 2 | Desk | 240 | 2026-02-11 |
| 106 | 4 | Lamp | 35 | 2026-02-20 |
| 107 | 1 | Lamp | 35 | 2026-03-03 |
| 108 | 3 | Chair | 120 | 2026-03-14 |
| 109 | NULL | Lamp | 35 | 2026-03-21 |
| 110 | 2 | Lamp | 35 | 2026-03-25 |
| all_rows | with_customer | customers |
|---|---|---|
| 10 | 9 | 4 |
COUNT(column), and it isn't a value for DISTINCT either.The DISTINCT that does nothing. DISTINCT right after SELECT removes duplicate result rows. A count is a single row, so there is nothing to remove, and no error tells you so.
Table: orders
| id | customer_id | product | amount | order_date |
|---|---|---|---|---|
| 101 | 1 | Desk | 240 | 2026-01-05 |
| 102 | 2 | Lamp | 35 | 2026-01-09 |
| 103 | 1 | Chair | 120 | 2026-01-17 |
| 104 | 3 | Monitor | 180 | 2026-02-02 |
| 105 | 2 | Desk | 240 | 2026-02-11 |
| 106 | 4 | Lamp | 35 | 2026-02-20 |
| 107 | 1 | Lamp | 35 | 2026-03-03 |
| 108 | 3 | Chair | 120 | 2026-03-14 |
| 109 | NULL | Lamp | 35 | 2026-03-21 |
| 110 | 2 | Lamp | 35 | 2026-03-25 |
| COUNT(customer_id) |
|---|
| 9 |
DISTINCT then looked at a result of one row. Move it inside the parentheses: COUNT(DISTINCT customer_id).COUNT(DISTINCT) with GROUP BY
With GROUP BY, the different values are counted inside each group.
Table: orders
| id | customer_id | product | amount | order_date |
|---|---|---|---|---|
| 101 | 1 | Desk | 240 | 2026-01-05 |
| 102 | 2 | Lamp | 35 | 2026-01-09 |
| 103 | 1 | Chair | 120 | 2026-01-17 |
| 104 | 3 | Monitor | 180 | 2026-02-02 |
| 105 | 2 | Desk | 240 | 2026-02-11 |
| 106 | 4 | Lamp | 35 | 2026-02-20 |
| 107 | 1 | Lamp | 35 | 2026-03-03 |
| 108 | 3 | Chair | 120 | 2026-03-14 |
| 109 | NULL | Lamp | 35 | 2026-03-21 |
| 110 | 2 | Lamp | 35 | 2026-03-25 |
| month | orders | customers |
|---|---|---|
| 2026-01 | 3 | 2 |
| 2026-02 | 3 | 3 |
| 2026-03 | 4 | 3 |
After a join
A join repeats a customer once per order. Count rows, or count a column of the customers table, and you get the number of orders. COUNT(DISTINCT c.id) counts each customer once. Count the id, not the name: two customers can share a name.
Tables: customers, orders
| id | name | city |
|---|---|---|
| 1 | Ava | Denver |
| 2 | Ben | Austin |
| 3 | Chloe | Denver |
| 4 | Dan | Boston |
| 5 | Eve | Austin |
| id | customer_id | product | amount | order_date |
|---|---|---|---|---|
| 101 | 1 | Desk | 240 | 2026-01-05 |
| 102 | 2 | Lamp | 35 | 2026-01-09 |
| 103 | 1 | Chair | 120 | 2026-01-17 |
| 104 | 3 | Monitor | 180 | 2026-02-02 |
| 105 | 2 | Desk | 240 | 2026-02-11 |
| 106 | 4 | Lamp | 35 | 2026-02-20 |
| 107 | 1 | Lamp | 35 | 2026-03-03 |
| 108 | 3 | Chair | 120 | 2026-03-14 |
| 109 | NULL | Lamp | 35 | 2026-03-21 |
| 110 | 2 | Lamp | 35 | 2026-03-25 |
| city | not_customers | customers |
|---|---|---|
| Austin | 3 | 1 |
| Boston | 1 | 1 |
| Denver | 5 | 2 |
c.id is never NULL here, so COUNT(c.id) counts the joined rows, which are orders. Ben's three orders make Austin look like it has three customers.COUNT(DISTINCT) on multiple columns
How many different pairs of customer and product are there? SQLite's COUNT takes one argument, and its documentation allows DISTINCT only in aggregate functions that take a single argument. Two columns are refused:
Table: orders
| id | customer_id | product | amount | order_date |
|---|---|---|---|---|
| 101 | 1 | Desk | 240 | 2026-01-05 |
| 102 | 2 | Lamp | 35 | 2026-01-09 |
| 103 | 1 | Chair | 120 | 2026-01-17 |
| 104 | 3 | Monitor | 180 | 2026-02-02 |
| 105 | 2 | Desk | 240 | 2026-02-11 |
| 106 | 4 | Lamp | 35 | 2026-02-20 |
| 107 | 1 | Lamp | 35 | 2026-03-03 |
| 108 | 3 | Chair | 120 | 2026-03-14 |
| 109 | NULL | Lamp | 35 | 2026-03-21 |
| 110 | 2 | Lamp | 35 | 2026-03-25 |
SQLite says: wrong number of arguments to function COUNT()
wrong number of arguments to function COUNT(). MySQL's documentation does accept a list here, COUNT(DISTINCT expr, expr...), and says it counts the combinations that don't contain NULL. The next query is a form SQLite accepts.Table: orders
| id | customer_id | product | amount | order_date |
|---|---|---|---|---|
| 101 | 1 | Desk | 240 | 2026-01-05 |
| 102 | 2 | Lamp | 35 | 2026-01-09 |
| 103 | 1 | Chair | 120 | 2026-01-17 |
| 104 | 3 | Monitor | 180 | 2026-02-02 |
| 105 | 2 | Desk | 240 | 2026-02-11 |
| 106 | 4 | Lamp | 35 | 2026-02-20 |
| 107 | 1 | Lamp | 35 | 2026-03-03 |
| 108 | 3 | Chair | 120 | 2026-03-14 |
| 109 | NULL | Lamp | 35 | 2026-03-21 |
| 110 | 2 | Lamp | 35 | 2026-03-25 |
| COUNT(*) |
|---|
| 9 |
WHERE customer_id IS NOT NULL inside the parentheses: 8.COUNT(DISTINCT) with CASE WHEN
To count only the values that meet a condition, put a CASE inside: COUNT(DISTINCT CASE WHEN amount >= 100 THEN customer_id END). For the rows that fail the condition, a CASE with no ELSE gives NULL, and NULL isn't counted. On this table it returns 3: customers 1, 2 and 3 each have an order of 100 or more. The last exercise uses it next to a plain COUNT(DISTINCT), which a WHERE couldn't do.
Practice: 4 exercises
Write the query in the box and press Run. Your result is compared with the right one, on this data and again on changed data, so a query with the values typed in won't pass. Column names don't matter; the columns and rows do.
Return one number: how many different products have been ordered.
Table: orders
| id | customer_id | product | amount | order_date |
|---|---|---|---|---|
| 101 | 1 | Desk | 240 | 2026-01-05 |
| 102 | 2 | Lamp | 35 | 2026-01-09 |
| 103 | 1 | Chair | 120 | 2026-01-17 |
| 104 | 3 | Monitor | 180 | 2026-02-02 |
| 105 | 2 | Desk | 240 | 2026-02-11 |
| 106 | 4 | Lamp | 35 | 2026-02-20 |
| 107 | 1 | Lamp | 35 | 2026-03-03 |
| 108 | 3 | Chair | 120 | 2026-03-14 |
| 109 | NULL | Lamp | 35 | 2026-03-21 |
| 110 | 2 | Lamp | 35 | 2026-03-25 |
Show the answer
SELECT COUNT(DISTINCT product)
FROM orders| COUNT(DISTINCT product) |
|---|
| 4 |
Ten orders, four products: Desk, Lamp, Chair and Monitor. DISTINCT inside the COUNT removes the repeats before counting.
For each product, return the product, its number of orders and the number of different customers who ordered it.
Table: orders
| id | customer_id | product | amount | order_date |
|---|---|---|---|---|
| 101 | 1 | Desk | 240 | 2026-01-05 |
| 102 | 2 | Lamp | 35 | 2026-01-09 |
| 103 | 1 | Chair | 120 | 2026-01-17 |
| 104 | 3 | Monitor | 180 | 2026-02-02 |
| 105 | 2 | Desk | 240 | 2026-02-11 |
| 106 | 4 | Lamp | 35 | 2026-02-20 |
| 107 | 1 | Lamp | 35 | 2026-03-03 |
| 108 | 3 | Chair | 120 | 2026-03-14 |
| 109 | NULL | Lamp | 35 | 2026-03-21 |
| 110 | 2 | Lamp | 35 | 2026-03-25 |
Show the answer
SELECT product, COUNT(*), COUNT(DISTINCT customer_id)
FROM orders
GROUP BY product| product | COUNT(*) | COUNT(DISTINCT customer_id) |
|---|---|---|
| Chair | 2 | 2 |
| Desk | 2 | 2 |
| Lamp | 5 | 3 |
| Monitor | 1 | 1 |
Lamp has 5 orders and 3 customers: the guest order has no customer, and customer 2 bought two. COUNT(customer_id) would say 4, because it skips the NULL but counts customer 2 twice.
For each city, return the city and the number of different customers from that city who have placed at least one order. Cities where nobody has ordered don't need to appear.
Tables: customers, orders
| id | name | city |
|---|---|---|
| 1 | Ava | Denver |
| 2 | Ben | Austin |
| 3 | Chloe | Denver |
| 4 | Dan | Boston |
| 5 | Eve | Austin |
| id | customer_id | product | amount | order_date |
|---|---|---|---|---|
| 101 | 1 | Desk | 240 | 2026-01-05 |
| 102 | 2 | Lamp | 35 | 2026-01-09 |
| 103 | 1 | Chair | 120 | 2026-01-17 |
| 104 | 3 | Monitor | 180 | 2026-02-02 |
| 105 | 2 | Desk | 240 | 2026-02-11 |
| 106 | 4 | Lamp | 35 | 2026-02-20 |
| 107 | 1 | Lamp | 35 | 2026-03-03 |
| 108 | 3 | Chair | 120 | 2026-03-14 |
| 109 | NULL | Lamp | 35 | 2026-03-21 |
| 110 | 2 | Lamp | 35 | 2026-03-25 |
Show the answer
SELECT c.city, COUNT(DISTINCT c.id)
FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.city| city | COUNT(DISTINCT c.id) |
|---|---|
| Austin | 1 |
| Boston | 1 |
| Denver | 2 |
The join leaves one row per order with a customer, so Ava appears three times. COUNT(DISTINCT c.id) counts her once: Denver 2, Austin 1, Boston 1. Eve is in Austin but has no order, and the inner join leaves her out.
For each month, return the month as text like 2026-03, the number of different customers who ordered in it, and the number of different customers who placed an order of 100 or more in it.
Table: orders
| id | customer_id | product | amount | order_date |
|---|---|---|---|---|
| 101 | 1 | Desk | 240 | 2026-01-05 |
| 102 | 2 | Lamp | 35 | 2026-01-09 |
| 103 | 1 | Chair | 120 | 2026-01-17 |
| 104 | 3 | Monitor | 180 | 2026-02-02 |
| 105 | 2 | Desk | 240 | 2026-02-11 |
| 106 | 4 | Lamp | 35 | 2026-02-20 |
| 107 | 1 | Lamp | 35 | 2026-03-03 |
| 108 | 3 | Chair | 120 | 2026-03-14 |
| 109 | NULL | Lamp | 35 | 2026-03-21 |
| 110 | 2 | Lamp | 35 | 2026-03-25 |
Show the answer
SELECT strftime('%Y-%m', order_date),
COUNT(DISTINCT customer_id),
COUNT(DISTINCT CASE WHEN amount >= 100 THEN customer_id END)
FROM orders
GROUP BY strftime('%Y-%m', order_date)| strftime('%Y-%m', order_date) | COUNT(DISTINCT customer_id) | COUNT(DISTINCT CASE WHEN amount >= 100 THEN customer_id END) |
|---|---|---|
| 2026-01 | 2 | 1 |
| 2026-02 | 3 | 2 |
| 2026-03 | 3 | 1 |
The CASE returns the customer for a big order and NULL otherwise, and COUNT(DISTINCT ...) counts the different customers among what's left: 1 in January, 2 in February, 1 in March. A WHERE amount >= 100 would also shrink the first count.
Questions people ask
What is the difference between COUNT(*), COUNT(column) and COUNT(DISTINCT column)?
COUNT(*) counts rows. COUNT(column) counts the rows where the column is not NULL. COUNT(DISTINCT column) counts the different non-NULL values. On the orders table of this page: 10, 9 and 4.
Does COUNT(DISTINCT) count NULL?
No. NULLs are left out, however many there are. If you want "no customer" to count as one more value, replace the NULL with a value that can't occur: COUNT(DISTINCT COALESCE(customer_id, -1)) gives 5 on this table. More on NULL.
How do I count distinct values across multiple columns?
In SQLite, COUNT(DISTINCT a, b) is an error (wrong number of arguments). Count the rows of a subquery instead: SELECT COUNT(*) FROM (SELECT DISTINCT a, b FROM t) AS x. Mind the NULLs: SELECT DISTINCT keeps a pair that contains a NULL, while MySQL's COUNT(DISTINCT a, b) is documented as counting only the combinations without one.
Is COUNT(DISTINCT x) the same as counting the rows of SELECT DISTINCT x?
Not when the column has NULLs. SELECT DISTINCT customer_id FROM orders returns five rows here, the four customers and one NULL, so counting its rows gives 5, while COUNT(DISTINCT customer_id) gives 4.
How do I use COUNT(DISTINCT) with CASE WHEN?
Put the CASE inside the count and let it return the value to count, or nothing: COUNT(DISTINCT CASE WHEN amount >= 100 THEN customer_id END). The rows that fail the condition become NULL and aren't counted. SQLite and PostgreSQL also document a FILTER clause for the same job: COUNT(DISTINCT customer_id) FILTER (WHERE amount >= 100). Both forms return 3 on this page's table.