SQL / AGGREGATION WITH GROUP BY
COUNT DISTINCT and counting each value once
Use COUNT(DISTINCT expr) to count each value once per group, know why NULLs drop out, and why per-group distinct counts never add up to the overall total.
What you will learn
- Read COUNT(DISTINCT x) as the size of one group's set of non-NULL x values
- Know that NULLs vanish before deduplication, so an all-NULL column counts 0
- Get a true overall distinct count instead of summing per-group distinct counts
- Count distinct column pairs with SELECT DISTINCT in a subquery, not COUNT(DISTINCT a,b)
Understanding COUNT DISTINCT and counting each value once
COUNT(DISTINCT expr) does three things in order: it evaluates expr for every row in the group, throws away the rows where the result is NULL, and counts how many different values remain. Because the last step is set membership, a customer who appears in fifty rows contributes exactly one to the result. The NULL step is why COUNT(DISTINCT status) over a column that is entirely NULL returns 0 and not 1: NULL never compares equal to anything, including another NULL, so it can never become a member of the set.
Under GROUP BY, that set is built independently for each group and discarded when the group ends. The same customer id can therefore belong to the north set and to the south set, and each group counts it once, which is correct for each output row and wrong the moment you add the rows together. An overall distinct count is a different question with a different answer, and it needs its own aggregate over the ungrouped rows, or a SELECT DISTINCT followed by a count.
DISTINCT applies to the expression you hand the aggregate, not to the stored column, so COUNT(DISTINCT lower(email)) and COUNT(DISTINCT email) can legitimately disagree on identical data. Deduplication then uses the type's own equality rules, which is why a case-insensitive collation, or the numeric values 1 and 1.0, collapse pairs you may have expected to stay separate. Deciding what counts as the same value is the real work here; the syntax is the easy part.
WITH orders(order_id, region, customer_id) AS (
VALUES (1, 'north', 10),
(2, 'north', 10),
(3, 'north', 11),
(4, 'south', 11),
(5, 'south', 12),
(6, 'south', 12),
(7, 'south', NULL)
)
SELECT region,
COUNT(*) AS rows_in_group,
COUNT(customer_id) AS ids_present,
COUNT(DISTINCT customer_id) AS distinct_customers
FROM orders
GROUP BY region
ORDER BY region;COUNT(DISTINCT expr) returns the size of the set of non-NULL expr values inside one group, so it counts values rather than rows and starts a fresh set for every group.
Worked examples
Daily distinct users do not sum to overall distinct users
Shows why adding up per-group COUNT(DISTINCT ...) results overstates the number of distinct values.
WITH logins(day, user_id) AS (
VALUES ('2026-03-01', 7),
('2026-03-01', 7),
('2026-03-01', 8),
('2026-03-02', 8),
('2026-03-02', 9),
('2026-03-02', 9)
), per_day AS (
SELECT day, COUNT(DISTINCT user_id) AS active_users
FROM logins
GROUP BY day
)
SELECT (SELECT SUM(active_users) FROM per_day) AS sum_of_daily,
(SELECT COUNT(DISTINCT user_id) FROM logins) AS distinct_overall;Example explained
Line 1per_day builds a separate set per day: 2026-03-01 sees {7, 8} and 2026-03-02 sees {8, 9}, so both days report 2.
Line 2SUM(active_users) is 4 because user 8 is a member of both sets and each group counted it once.
Line 3The second subquery builds a single set over all six rows, {7, 8, 9}, so the honest overall figure is 3.
Line 4Distinct counts are not additive across groups, so a total always needs its own pass over the underlying rows.
Deduplicating an expression instead of the column
Demonstrates that DISTINCT compares the result of the expression, and that NULL rows are removed before deduplication.
WITH signups(email) AS (
VALUES ('Ada@example.com'),
('ada@example.com'),
('bob@example.com'),
(CAST(NULL AS text))
)
SELECT COUNT(*) AS rows_total,
COUNT(DISTINCT email) AS distinct_raw,
COUNT(DISTINCT lower(email)) AS distinct_normalized
FROM signups;Example explained
Line 1COUNT(*) is 4 because the NULL row is still a row; only value-based counts care about NULL.
Line 2COUNT(DISTINCT email) is 3: the NULL is dropped, and under a case-sensitive collation 'Ada@example.com' and 'ada@example.com' are two different values.
Line 3COUNT(DISTINCT lower(email)) evaluates lower() first, so both spellings become 'ada@example.com' and the set shrinks to two members.
Counting distinct combinations of two columns
Contrasts gluing columns into one string with deduplicating the pair properly.
WITH bookings(building, room) AS (
VALUES ('1', '02'),
('10', '2'),
('1', '02')
), pairs AS (
SELECT DISTINCT building, room FROM bookings
)
SELECT (SELECT COUNT(DISTINCT building || room) FROM bookings) AS glued_key,
(SELECT COUNT(*) FROM pairs) AS true_pairs;Example explained
Line 1building || room produces one string per row, so DISTINCT compares strings: '1' with '02' and '10' with '2' both become '102' and merge into a single value.
Line 2The pairs CTE deduplicates the two columns together, so COUNT(*) over it reports the real number of combinations, 2.
Line 3COUNT(DISTINCT a, b) is a MySQL extension; PostgreSQL needs the row form COUNT(DISTINCT (a, b)) and SQLite rejects both, which makes the subquery pattern the portable one.
Line 4A NULL in either column would make the glued key NULL and remove that row from the count, while SELECT DISTINCT keeps it and treats NULLs as equal when deduplicating.
Important notes
COUNT(DISTINCT ...) must remember every value it has already seen, so it needs a sort or hash rather than a plain row count, and several distinct counts in one query mean several deduplication passes; some engines offer approximate versions such as approx_count_distinct or HyperLogLog when an estimate is enough.
DISTINCT inside an aggregate is rejected together with an OVER clause in PostgreSQL and SQL Server, so COUNT(DISTINCT x) OVER (PARTITION BY ...) is not available as a running distinct count.
Common mistakes
Expecting NULL to count as one distinct value: a column that is all NULL reports 0, so a 'distinct payment methods' figure silently ignores rows where the method was never recorded and never reconciles with COUNT(*).
Summing per-group distinct counts into a grand total: a customer active in two regions is counted twice, so the total exceeds the number of customers that exist and inflates further as you add more groups.
Writing COUNT(DISTINCT a, b) for distinct pairs and then patching it with a || b: the first form errors outside MySQL, and the concatenation makes ('1','02') and ('10','2') collide while any NULL part turns the whole key into NULL and removes the row.
Try it yourself
Change, predict, then run
Build a CTE of eight (region, customer_id) rows in which one customer appears in two regions and one row has a NULL customer_id, then write one query returning COUNT(*), COUNT(customer_id) and COUNT(DISTINCT customer_id) per region and a second returning the same three numbers for the whole table. Confirm that the per-region distinct counts add up to more than the overall distinct count and explain which customer causes the gap.
Open the SQL workspaceCheck your understanding
A report groups orders by region and returns COUNT(DISTINCT customer_id) for each region. Adding those numbers together gives a figure larger than the number of rows in the customers table. What explains it?
- COUNT(DISTINCT ...) counts rows rather than values, so repeat orders inflate every region.
- Customers who ordered in more than one region belong to more than one group's set, and each group counts them once.
- Rows with a NULL customer_id add one extra distinct customer to each region.
- COUNT(DISTINCT ...) ignores GROUP BY and counts over the whole table for every row.
Show answer
Each group builds its own set of distinct values, so a customer active in three regions contributes 1 to three groups and 3 to the sum; the per-region numbers are right and the addition is the mistake. The NULL option is backwards: NULLs are discarded before deduplication, so they can only push a distinct count below COUNT(*), never above it. Repeat orders cannot be the cause either, since DISTINCT already collapses a customer's many rows into one.