Aggregation Updated November 2026

COUNT(DISTINCT)

COUNT(DISTINCT column) returns the number of different non-NULL values in the column: SELECT COUNT(DISTINCT customer_id) FROM orders counts customers who ordered at least once, however many orders each placed. With GROUP BY it counts distinct values per group.

Syntax

COUNT(DISTINCT column)

Examples

SELECT COUNT(DISTINCT customer_id) AS buyers FROM orders;

Result: buyers 42

SELECT country, COUNT(DISTINCT city) AS cities FROM customers GROUP BY country;

Result: country | cities Aruba | 6 Curacao | 4

SELECT customer_id, COUNT(DISTINCT strftime('%Y-%m', order_date)) AS active_months
FROM orders GROUP BY customer_id HAVING active_months >= 3;

Result: Customers active in three or more different months (SQLite)

In PostgreSQL use to_char(order_date, 'YYYY-MM') and repeat the expression in HAVING.

Common mistakes

COUNT(DISTINCT a, b) with two columns is a syntax error in SQLite and PostgreSQL; use a subquery with SELECT DISTINCT a, b.
Writing DISTINCT COUNT(column) puts DISTINCT on the result rows, not the values, and gives the wrong number; the DISTINCT goes inside the parentheses.
Expecting NULL to be counted as a value; COUNT(DISTINCT col) ignores NULL, so a column with values 1, 2, NULL gives 2.

Practice COUNT(DISTINCT)

These drills check your answer on live data, free, no account needed.

Customers who orderedHow many different customers have placed at least one order? Return a single number.
Active in several monthsFor customers who ordered in at least two different months, return the customer_id and the number of different months they ordered in. Count every order, whatever the status.

Questions

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

COUNT(column) counts every non-NULL value, duplicates included; COUNT(DISTINCT column) counts each value once.

How do I count distinct values across multiple columns?

Wrap a SELECT DISTINCT over those columns in a subquery and COUNT(*) its rows. PostgreSQL also allows COUNT(DISTINCT (a, b)).

COUNTCOUNT(*) counts rows, COUNT(column) counts non-NULL values, COUNT(DISTINCT column) counts unique values. Examples with WHERE, GROUP BY and conditional counts.
DISTINCTSELECT DISTINCT returns each distinct value or combination once. List unique countries, count distinct values, and see why DISTINCT dedupes whole rows.
GROUP BYGROUP BY collapses rows that share a value into one row per group so you can count, sum or average them. One or several columns, the must-appear rule, HAVING.
HAVINGHAVING filters the groups made by GROUP BY using aggregate conditions such as COUNT(*) > 1 or SUM(amount) > 1000. Examples, HAVING vs WHERE, both together.