Module 3: Summaries · Lesson 3.4 · about 25 min
COUNT DISTINCT and counting the right thing
COUNT(*) counts rows, COUNT(DISTINCT x) counts different values. Learn which one a question needs, why distinct counts never add up across groups, and how messy values inflate them.
Module 3 · about 25 minutes · read first: 3.3. You’ll be able to choose between counting rows and counting different values, explain why distinct counts don’t add up, and spot values that are only nearly the same.
In everyday terms: a gym. Counting visits is COUNT(*); counting members who came is COUNT(DISTINCT member). Monday’s members plus Tuesday’s members overcounts everyone who came on both days.
1. Why this matters
“How many customers did we have last month?” sounds simple, but COUNT(*) on orders gives orders, not customers, and the two can differ by a factor of six. Active customers, unique visitors and products sold are all distinct counts, and they are among the most reported (and most misreported) numbers in any business.
2. Rows vs different values
COUNT(*)counts rows.COUNT(x)counts rows where x is not NULL.COUNT(DISTINCT x)counts the different non-NULL values of x.
-- try
SELECT COUNT(*) AS orders,
COUNT(customer_id) AS orders_with_customer,
COUNT(DISTINCT customer_id) AS customers
FROM mini_orders
Six orders, but only four different customers (1, 2, 3 and 9), because customers 1 and 3 ordered twice. Press Show how it runs and look at the customer_id column: count the different numbers yourself.
3. The same question on Northbay
-- try
SELECT COUNT(*) AS orders,
COUNT(customer_id) AS member_orders,
COUNT(DISTINCT customer_id) AS customers_who_ordered
FROM orders
4,064 orders, of which 3,582 were by members, placed by 600 different members. On average a member placed 3,582 / 600 ≈ 6 orders over the two years.
Pick the count that matches the noun in the question: “how many orders” is COUNT(*); “how many customers” is COUNT(DISTINCT customer_id).
- COUNT(*)
- Number of rows (orders, visits, lines).
- COUNT(DISTINCT x)
- Number of different non-NULL values of x (customers, products).
- Additive
- A number you can add across groups: row counts, sums.
- Non-additive
- A number you cannot add across groups: distinct counts, averages.
- Physical meaning
- COUNT(DISTINCT) puts each value through a sieve that lets it pass only once, then counts what passed. Someone who appears in two groups passes once in each group but only once overall.
“Customers in 2024 plus customers in 2025 is how many customers we had.” Anyone who bought in both years is counted twice. 561 + 584 = 1,145, but only 600 customers ordered at all. Never add distinct counts; count again over the combined rows.
Watch it happen, one step at a time (press Show how it runs on any live box to see real rows):
| month | customer |
|---|---|
| Jan | Asha |
| Jan | Ben |
| Jan | Asha |
| Feb | Ben |
| Feb | Chen |
| month | customers |
|---|---|
| Jan | 2 |
| Feb | 2 |
4. Distinct counts don’t add up
Count customers per year:
-- try
SELECT strftime('%Y', order_date) AS year,
COUNT(*) AS orders,
COUNT(DISTINCT customer_id) AS customers
FROM orders
GROUP BY strftime('%Y', order_date)
The orders column adds up: 1,801 + 2,263 = 4,064. The customers column doesn’t: 561 + 584 = 1,145, but only 600 customers exist. Anyone who ordered in both years is counted once in each year. The same happens with channels:
-- try
SELECT channel, COUNT(DISTINCT customer_id) AS customers
FROM orders
GROUP BY channel
482 + 594 = 1,076 again overshoots 600, because many people shop both online and in store. Never add up distinct counts from different groups; count again over the combined rows. In Power BI and Tableau, this is why “distinct count” totals at the bottom of a table often look smaller than the sum of the rows above: they’re right.
5. Nearly the same is not the same
DISTINCT compares values exactly. Spaces and capitals make new values:
-- try
SELECT COUNT(DISTINCT city) AS raw_cities,
COUNT(DISTINCT TRIM(city)) AS clean_cities
FROM customers
22 “cities” before trimming, 11 after. The three customers who signed up twice used their email in capitals:
-- try
SELECT COUNT(email) AS emails,
COUNT(DISTINCT email) AS distinct_emails,
COUNT(DISTINCT LOWER(email)) AS distinct_people
FROM customers
566 distinct emails, but only 563 once capitals are ignored. Cleaning before counting is so common that Module 7 spends two lessons on it.
The opposite trap: don’t count people by name. There are 603 customer rows but only 486 different first-and-last-name pairs, because different people share names. Count by the key (customer_id) or something truly unique (email), not by a label.
6. DISTINCT inside other aggregates
DISTINCT works in SUM and AVG too, but it is rarely what you want there: SUM(DISTINCT amount) adds each different amount once, so two $45 Kettles count as one $45.
-- try
SELECT SUM(amount) AS total, SUM(DISTINCT amount) AS distinct_total
FROM mini_orders
265 vs 220. If you find yourself reaching for SUM(DISTINCT …), the real problem is usually duplicated rows from a join (Lesson 4.5).
Maths and principle
Principle: COUNT(*) is the size of a bag (repeats count); COUNT(DISTINCT x) is the size of a set (each value once). The set of values in a column is δ(πx(T)), and its size is the column’s cardinality.
Equation: row counts are additive over a partition: |A ∪ B| = |A| + |B| when A and B share no rows. Distinct counts are not: for the sets of customers, |A ∪ B| = |A| + |B| − |A ∩ B|. With 2024 and 2025: 600 = 561 + 584 − |both|, so 545 customers ordered in both years.
Analogy: counting visits to a gym vs counting members who visited. Monday’s members plus Tuesday’s members overcounts anyone who came both days. It breaks down a little with NULL: COUNT(DISTINCT) ignores NULL, while SELECT DISTINCT keeps one NULL row.
Try it: online 482 customers, store 594, total 600. How many shopped both ways? (482 + 594 − 600 = 476.)
7. Worked example
Question: how many returns were there, on how many different orders, and how many different products?
- Rows:
COUNT(*)over returns. - Orders:
COUNT(DISTINCT order_id). - Products:
COUNT(DISTINCT product_id).
-- try
SELECT COUNT(*) AS returned_lines,
COUNT(DISTINCT order_id) AS orders_with_a_return,
COUNT(DISTINCT product_id) AS products_returned
FROM returns
- 204 returned lines on 201 orders, so 3 orders had two products sent back. If the question is “what share of orders had a return?”, the top of the fraction is 201, not 204. Saying 204 would overstate it slightly; on bigger data the gap can be large.
8. Practice
-- exercise: How many different products have ever been ordered? Return one number.
-- hint: COUNT(DISTINCT product_id) FROM order_items.
SELECT COUNT(product_id) AS products
FROM order_items
-- answer:
SELECT COUNT(DISTINCT product_id) AS products
FROM order_items
-- exercise: For each store_id, count the different customers who ordered there. Show the top 3 stores by that number.
-- hint: GROUP BY store_id, COUNT(DISTINCT customer_id), ORDER BY ... DESC, LIMIT 3.
SELECT store_id, COUNT(*) AS customers
FROM orders
GROUP BY store_id
ORDER BY customers DESC
LIMIT 3
-- answer:
SELECT store_id, COUNT(DISTINCT customer_id) AS customers
FROM orders
GROUP BY store_id
ORDER BY customers DESC
LIMIT 3
-- exercise: In how many different months (year and month) did Northbay take orders? Use strftime('%Y-%m', order_date).
-- hint: COUNT(DISTINCT strftime('%Y-%m', order_date)).
SELECT COUNT(DISTINCT strftime('%m', order_date)) AS months
FROM orders
-- answer:
SELECT COUNT(DISTINCT strftime('%Y-%m', order_date)) AS months
FROM orders
- Match the count to the noun: “how many orders” is COUNT(*); “how many customers” is COUNT(DISTINCT customer_id).
- COUNT(DISTINCT x) ignores NULLs.
- Distinct counts don’t add up across groups; overlap = sum of parts − whole.
- Clean before counting: trailing spaces and capitals make new “different” values.
- Count people by a key, not by a name; SUM(DISTINCT) is almost never what you want.
New words: COUNT DISTINCT, cardinality, additive, non-additive, inclusion-exclusion.
Check yourself
Sources
- SQLite documentation, Built-in Aggregate Functions (count with DISTINCT) https://www.sqlite.org/lang_aggfunc.html
- Microsoft Learn, COUNT (Transact-SQL) https://learn.microsoft.com/en-us/sql/t-sql/functions/count-transact-sql
- Kimball Group, Additive, Semi-Additive, and Non-Additive Facts https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/additive-semi-additive-non-additive-fact/
Your ticks stay in this browser. Save them as a file on the course home.
Updated 2026-11-22. Northbay Retail is a made-up company; its data is generated in your browser. Queries run on your device and nothing you type is sent anywhere.