COUNT(DISTINCT column) counts how many different values a column holds: a value that appears ten times counts once, and NULLs don’t count at all. The examples work in PostgreSQL, MySQL, SQLite and SQL Server.
How to use COUNT DISTINCT
Put DISTINCT inside COUNT(), in front of the column or expression to count:
COUNT(DISTINCT expression)
It’s easiest to see next to the other two forms of COUNT. COUNT(*) counts rows, COUNT(column) counts rows where the column isn’t NULL, and COUNT(DISTINCT column) counts the different non-NULL values:
-- Input: orders
-- order_id | customer_id
-- ---------+------------
-- 1 | 1042
-- 2 | 1077
-- 3 | 1042
-- 4 | NULL
SELECT COUNT(*) AS all_rows,
COUNT(customer_id) AS with_customer,
COUNT(DISTINCT customer_id) AS customers
FROM orders;
-- Output:
-- all_rows | with_customer | customers
-- ---------+---------------+----------
-- 4 | 3 | 2
Four orders came from two customers: 1042 ordered twice, and order 4 has no customer at all.
Rules to know
- NULLs aren’t counted. If a missing value should count as one more, add it yourself:
-- Input: the orders table above
SELECT COUNT(DISTINCT customer_id)
+ MAX(CASE WHEN customer_id IS NULL THEN 1 ELSE 0 END) AS customers_and_guests
FROM orders;
-- Output: 3
-
MySQL and SQL Server ignore case by default, so they count
'SQL'and'sql'as one value. CountLOWER(tag)when case shouldn’t matter:
-- Input: tags
-- post_id | tag
-- --------+---------
-- 1 | SQL
-- 2 | sql
-- 3 | Postgres
SELECT COUNT(DISTINCT tag) FROM tags;
-- Output: 3 (2 in MySQL and SQL Server)
SELECT COUNT(DISTINCT LOWER(tag)) FROM tags;
-- Output: 2
-
DISTINCT works inside
SUMandAVGtoo, but it drops repeated values, not repeated rows. Two different products with the same price are added only once:
-- Input: order_lines
-- product | price
-- ---------+------
-- Pen | 2.00
-- Pencil | 2.00
-- Notebook | 5.00
SELECT SUM(price) AS total, SUM(DISTINCT price) AS distinct_total FROM order_lines;
-- Output:
-- total | distinct_total
-- ------+---------------
-- 9.00 | 7.00
Alternative syntax
COUNT(DISTINCT ...) takes one column. To count the different combinations of several columns, count the rows of a DISTINCT subquery. PostgreSQL and MySQL also have shorthands:
-- Input: orders
-- order_id | customer_id | product_id
-- ---------+-------------+-----------
-- 1 | 1 | 23
-- 2 | 12 | 3
-- 3 | 1 | 23
-- 4 | 12 | 5
SELECT COUNT(*) FROM (SELECT DISTINCT customer_id, product_id FROM orders) AS pairs;
-- Output: 3
-- PostgreSQL
SELECT COUNT(DISTINCT (customer_id, product_id)) FROM orders;
-- Output: 3
-- MySQL
SELECT COUNT(DISTINCT customer_id, product_id) FROM orders;
-- Output: 3
You’ll also see the columns glued into one string with CONCAT(). It needs a separator: without one, customer 1 with product 23 and customer 12 with product 3 both become '123':
-- Input: the orders table above
SELECT COUNT(DISTINCT CONCAT(customer_id, product_id)) FROM orders;
-- Output: 2
SELECT COUNT(DISTINCT CONCAT(customer_id, '_', product_id)) FROM orders;
-- Output: 3
An exact distinct count has to sort or hash every value, which gets slow on very large tables. SQL Server has APPROX_COUNT_DISTINCT(), which is faster and stays within 2% of the exact count 97% of the time:
-- SQL Server
-- Input: the orders table above
SELECT APPROX_COUNT_DISTINCT(customer_id) AS customers FROM orders;
-- Output: 2
When should you use COUNT DISTINCT?
Use COUNT DISTINCT when the question is “how many different …?” rather than “how many rows?”. Most of those questions fall into one of these four.
Count unique visitors, customers or products
A log of visits, orders or sales records the same visitor, customer or product over and over. COUNT DISTINCT on its ID turns those rows into a headcount.
Count unique values in each group
Add GROUP BY and you get one distinct count per group: different students per course, different products per vendor, different customers per month.
Count unique combinations of columns
Sometimes two rows are only “the same” when two columns both match: the same user with the same kind of issue, or the same route between two airports. Count the combinations with one of the multi-column forms under “Alternative syntax”.
Count unique values that meet a condition
Put a CASE expression inside COUNT(DISTINCT ...) to count only some of the rows. The CASE returns NULL for the rows you don’t want, and NULLs aren’t counted, so one query can return several filtered counts side by side.
Real examples of COUNT DISTINCT
Monthly active users
Count the users who did anything in September, however many times they came back:
-- Input: events
-- event_id | user_id | event_date
-- ---------+---------+-----------
-- 1 | 301 | 2026-08-30
-- 2 | 302 | 2026-09-02
-- 3 | 302 | 2026-09-15
-- 4 | 305 | 2026-09-21
-- 5 | 302 | 2026-10-01
SELECT COUNT(DISTINCT user_id) AS active_users
FROM events
WHERE event_date >= '2026-09-01' AND event_date < '2026-10-01';
-- Output: 2
Products sold by each vendor
COUNT(*) gives each vendor’s number of sales, and COUNT(DISTINCT product) how many different products those sales covered:
-- Input: sales
-- vendor | product | color
-- -----------+----------+------
-- Acme | Mug | Red
-- Acme | Mug | Blue
-- Acme | Mug | Red
-- Acme | Tote Bag | Red
-- Brightside | Notebook | Black
SELECT vendor, COUNT(*) AS sales, COUNT(DISTINCT product) AS products
FROM sales
GROUP BY vendor;
-- Output:
-- vendor | sales | products
-- -----------+-------+---------
-- Acme | 4 | 2
-- Brightside | 1 | 1
Routes an airline flies
A route is an origin and a destination together. Two flights from LHR to JFK are one route, but JFK to LHR is another:
-- Input: flights
-- flight_no | origin | destination
-- ----------+--------+------------
-- 101 | LHR | JFK
-- 105 | LHR | JFK
-- 102 | JFK | LHR
-- 301 | LHR | LAX
SELECT COUNT(*) AS routes
FROM (SELECT DISTINCT origin, destination FROM flights) AS r;
-- Output: 3
Customers by the price of what they bought
Count the customers who bought products 100 to 200, then split them by the price of their orders. Customer 7 placed two cheap orders but counts once, and customer 15’s product is out of range:
-- Input: orders
-- order_id | customer_id | product_id | price
-- ---------+-------------+------------+-------
-- 1 | 7 | 105 | 25.00
-- 2 | 7 | 142 | 40.00
-- 3 | 9 | 150 | 120.00
-- 4 | 12 | 188 | 310.00
-- 5 | 15 | 260 | 30.00
SELECT
COUNT(DISTINCT customer_id) AS customers,
COUNT(DISTINCT CASE WHEN price BETWEEN 1 AND 50 THEN customer_id END) AS low_spenders,
COUNT(DISTINCT CASE WHEN price BETWEEN 51 AND 150 THEN customer_id END) AS mid_spenders,
COUNT(DISTINCT CASE WHEN price > 150 THEN customer_id END) AS high_spenders
FROM orders
WHERE product_id BETWEEN 100 AND 200;
-- Output:
-- customers | low_spenders | mid_spenders | high_spenders
-- ----------+--------------+--------------+--------------
-- 3 | 1 | 1 | 1
A customer with orders in two price bands is counted in both.
Further reading about COUNT DISTINCT
On this blog:
-
SQL GROUP BY guide:
COUNT,SUMandAVGfor each group - SQL find duplicates guide: find the values that appear more than once
- SQL remove duplicates guide: delete the extra copies
- SQL CASE statement guide: the conditions behind conditional counts
-
Having vs Where in SQL: filter on a count with
HAVING - SQL cheat sheet: aggregates and the rest of SQL on one page
Official documentation:
- PostgreSQL: aggregate expressions
- MySQL: COUNT(DISTINCT)
- SQLite: count()
- SQL Server: COUNT and APPROX_COUNT_DISTINCT
Beekeeper Studio Is A Free & Open Source Database GUI
Best SQL query & editor tool I have ever used. It provides everything I need to manage my database. - ⭐⭐⭐⭐⭐ Mit
Beekeeper Studio is fast, intuitive, and easy to use. Beekeeper supports loads of databases, and works great on Windows, Mac and Linux.
What Users Say About Beekeeper Studio
"Beekeeper Studio completely replaced my old SQL workflow. It's fast, intuitive, and makes database work enjoyable again."
"I've tried many database GUIs, but Beekeeper strikes the perfect balance between features and simplicity. It just works."