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.

Skip to the 4 exercises ↓

Syntax

SELECT COUNT(DISTINCT customer_id)
FROM orders
COUNT(*)
The number of rows.
COUNT(customer_id)
The number of rows where customer_id is not NULL.
COUNT(DISTINCT customer_id)
The number of different non-NULL values of customer_id. DISTINCT goes 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.

customers
idnamecity
1AvaDenver
2BenAustin
3ChloeDenver
4DanBoston
5EveAustin
orders
idcustomer_idproductamountorder_date
1011Desk2402026-01-05
1022Lamp352026-01-09
1031Chair1202026-01-17
1043Monitor1802026-02-02
1052Desk2402026-02-11
1064Lamp352026-02-20
1071Lamp352026-03-03
1083Chair1202026-03-14
109NULLLamp352026-03-21
1102Lamp352026-03-25
Live queryThree counts of the same table
Table: orders
orders
idcustomer_idproductamountorder_date
1011Desk2402026-01-05
1022Lamp352026-01-09
1031Chair1202026-01-17
1043Monitor1802026-02-02
1052Desk2402026-02-11
1064Lamp352026-02-20
1071Lamp352026-03-03
1083Chair1202026-03-14
109NULLLamp352026-03-21
1102Lamp352026-03-25
Ctrl/⌘ + Enter to run
Result · 1 row
all_rowswith_customercustomers
1094

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.

Live queryDISTINCT in the wrong place
Table: orders
orders
idcustomer_idproductamountorder_date
1011Desk2402026-01-05
1022Lamp352026-01-09
1031Chair1202026-01-17
1043Monitor1802026-02-02
1052Desk2402026-02-11
1064Lamp352026-02-20
1071Lamp352026-03-03
1083Chair1202026-03-14
109NULLLamp352026-03-21
1102Lamp352026-03-25
Ctrl/⌘ + Enter to run
Result · 1 row
COUNT(customer_id)
9

COUNT(DISTINCT) with GROUP BY

With GROUP BY, the different values are counted inside each group.

Live queryOrders and customers per month
Table: orders
orders
idcustomer_idproductamountorder_date
1011Desk2402026-01-05
1022Lamp352026-01-09
1031Chair1202026-01-17
1043Monitor1802026-02-02
1052Desk2402026-02-11
1064Lamp352026-02-20
1071Lamp352026-03-03
1083Chair1202026-03-14
109NULLLamp352026-03-21
1102Lamp352026-03-25
Ctrl/⌘ + Enter to run
Result · 3 rows
monthorderscustomers
2026-0132
2026-0233
2026-0343

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.

Live queryCustomers per city: COUNT(c.id) counts orders
Tables: customers, orders
customers
idnamecity
1AvaDenver
2BenAustin
3ChloeDenver
4DanBoston
5EveAustin
orders
idcustomer_idproductamountorder_date
1011Desk2402026-01-05
1022Lamp352026-01-09
1031Chair1202026-01-17
1043Monitor1802026-02-02
1052Desk2402026-02-11
1064Lamp352026-02-20
1071Lamp352026-03-03
1083Chair1202026-03-14
109NULLLamp352026-03-21
1102Lamp352026-03-25
Ctrl/⌘ + Enter to run
Result · 3 rows
citynot_customerscustomers
Austin31
Boston11
Denver52

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:

Live queryTwo columns inside COUNT(DISTINCT)
Table: orders
orders
idcustomer_idproductamountorder_date
1011Desk2402026-01-05
1022Lamp352026-01-09
1031Chair1202026-01-17
1043Monitor1802026-02-02
1052Desk2402026-02-11
1064Lamp352026-02-20
1071Lamp352026-03-03
1083Chair1202026-03-14
109NULLLamp352026-03-21
1102Lamp352026-03-25
Ctrl/⌘ + Enter to run

SQLite says: wrong number of arguments to function COUNT()

Live queryCount the rows of a SELECT DISTINCT
Table: orders
orders
idcustomer_idproductamountorder_date
1011Desk2402026-01-05
1022Lamp352026-01-09
1031Chair1202026-01-17
1043Monitor1802026-02-02
1052Desk2402026-02-11
1064Lamp352026-02-20
1071Lamp352026-03-03
1083Chair1202026-03-14
109NULLLamp352026-03-21
1102Lamp352026-03-25
Ctrl/⌘ + Enter to run
Result · 1 row
COUNT(*)
9

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.

Exercise 1How many different products?

Return one number: how many different products have been ordered.

Table: orders
orders
idcustomer_idproductamountorder_date
1011Desk2402026-01-05
1022Lamp352026-01-09
1031Chair1202026-01-17
1043Monitor1802026-02-02
1052Desk2402026-02-11
1064Lamp352026-02-20
1071Lamp352026-03-03
1083Chair1202026-03-14
109NULLLamp352026-03-21
1102Lamp352026-03-25
Ctrl/⌘ + Enter to run
Show the answer
SELECT COUNT(DISTINCT product)
FROM orders
Result · 1 row
COUNT(DISTINCT product)
4

Ten orders, four products: Desk, Lamp, Chair and Monitor. DISTINCT inside the COUNT removes the repeats before counting.

Exercise 2Orders and customers per product

For each product, return the product, its number of orders and the number of different customers who ordered it.

Table: orders
orders
idcustomer_idproductamountorder_date
1011Desk2402026-01-05
1022Lamp352026-01-09
1031Chair1202026-01-17
1043Monitor1802026-02-02
1052Desk2402026-02-11
1064Lamp352026-02-20
1071Lamp352026-03-03
1083Chair1202026-03-14
109NULLLamp352026-03-21
1102Lamp352026-03-25
Ctrl/⌘ + Enter to run
Show the answer
SELECT product, COUNT(*), COUNT(DISTINCT customer_id)
FROM orders
GROUP BY product
Result · 4 rows
productCOUNT(*)COUNT(DISTINCT customer_id)
Chair22
Desk22
Lamp53
Monitor11

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.

Exercise 3Customers per city, after a join

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
customers
idnamecity
1AvaDenver
2BenAustin
3ChloeDenver
4DanBoston
5EveAustin
orders
idcustomer_idproductamountorder_date
1011Desk2402026-01-05
1022Lamp352026-01-09
1031Chair1202026-01-17
1043Monitor1802026-02-02
1052Desk2402026-02-11
1064Lamp352026-02-20
1071Lamp352026-03-03
1083Chair1202026-03-14
109NULLLamp352026-03-21
1102Lamp352026-03-25
Ctrl/⌘ + Enter to run
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
Result · 3 rows
cityCOUNT(DISTINCT c.id)
Austin1
Boston1
Denver2

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.

Exercise 4Customers, and customers with a big order, per month

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
orders
idcustomer_idproductamountorder_date
1011Desk2402026-01-05
1022Lamp352026-01-09
1031Chair1202026-01-17
1043Monitor1802026-02-02
1052Desk2402026-02-11
1064Lamp352026-02-20
1071Lamp352026-03-03
1083Chair1202026-03-14
109NULLLamp352026-03-21
1102Lamp352026-03-25
Ctrl/⌘ + Enter to run
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)
Result · 3 rows
strftime('%Y-%m', order_date)COUNT(DISTINCT customer_id)COUNT(DISTINCT CASE WHEN amount >= 100 THEN customer_id END)
2026-0121
2026-0232
2026-0331

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.

Keep going

Lesson 2 is seven drills on an orders table: COUNT, SUM and AVG, GROUP BY, HAVING, and it ends on COUNT(DISTINCT). It's free, like lessons 1, 3 and 4 and the level test.

Related

SQL GROUP BY, with live queriesSQL WHERE vs HAVING, with live queriesSQL NULL: IS NULL, COALESCE and the NOT IN trapSQL JOIN: INNER and LEFT, with live queriesSQL GROUP BY practiceSQL skills testSQL reference with queries you can runSQL practice online

Written and tested by Nico, who builds SheetDrill (about). Every query on this page was run on SQLite, and every exercise checked against right and wrong answers, before the page was published. Other databases differ in places; where they do, the page says so. Checked against: SQLite documentation: Built-in Aggregate Functions, SQLite documentation: SELECT (DISTINCT and NULLs), SQLite documentation: SQL Language Expressions (CASE), PostgreSQL documentation: Value Expressions (aggregate expressions, FILTER), MySQL 8.4 Reference Manual: Aggregate Function Descriptions. Last checked November 2026.