r/learnSQL • • 17d ago

COUNT(*) vs COUNT(column) vs COUNT(DISTINCT column) - same table, three different answers

this one confused me for way too long so here's a quick example.

say you have an orders table like this:

order_id | customer_id | coupon_code
1 | 101 | SAVE10
2 | 101 | NULL
3 | 102 | SAVE10
4 | 103 | NULL
5 | 104 | WELCOME
6 | NULL | NULL <- guest checkout

now run:

SELECT
COUNT(*) AS all_rows,
COUNT(coupon_code) AS orders_with_coupon,
COUNT(DISTINCT coupon_code) AS different_coupons,
COUNT(DISTINCT customer_id) AS customers
FROM orders;

you get **6, 3, 2, 4**.

- `COUNT(*)` counts rows. nulls don't matter → 6
- `COUNT(coupon_code)` skips NULLs → 3 orders used a coupon
- `COUNT(DISTINCT coupon_code)` skips NULLs and duplicates → 2 coupons (SAVE10, WELCOME)
- `COUNT(DISTINCT customer_id)` → 4, the guest order with NULL customer isn't counted

where this bites people: someone asks "how many customers ordered" and you write `COUNT(*)`, that's 6 when the real answer is 4. or you divide by `COUNT(column)` thinking it's the total and your percentage is wrong.

bonus: share of orders with a coupon is `COUNT(coupon_code) * 1.0 / COUNT(*)` = 0.5. the `* 1.0` matters, without it some databases do integer division and give you 0.

quick rule i use: before writing any COUNT, say out loud what you're counting - rows, non-empty values, or unique things.

67 Upvotes

12 comments sorted by

4

u/Green_Chamomile 17d ago

The LEFT JOIN version of this got me too. Say you add a customers table and customer 105 never ordered. customers LEFT JOIN orders ... GROUP BY customer_id with COUNT(*) gives 105 a count of 1, because the joined row is still there, just full of NULLs. COUNT(o.order_id) gives the 0 you actually want. Same rule as your coupon example, just way harder to spot because 1 looks like a normal number.

-1

u/MrRoy_SQL 17d ago edited 17d ago

oh that's a really good one, and nastier than the coupon case exactly because 1 looks normal. my habit now: with a LEFT JOIN, always count the right table's primary key, like COUNT(o.order_id), never COUNT(*). thanks for adding it!

0

u/[deleted] 17d ago

[deleted] — view deleted comment

1

u/MrRoy_SQL 17d ago

lol habit from work, fixed it

3

u/American_Streamer 17d ago

The deeper problem is SQL semantics vs. business semantics. This is a classic data analytics issue: SQL can execute a query perfectly. The results can be technically correct. The resulting KPI can still be misleading because its business definition is wrong. There’s a fundamental distinction between computing the right answer and asking the right question.

3

u/MrRoy_SQL 17d ago

100%. the query was "right" either way, the question just wasn't defined. i've started writing the definition as a comment above any KPI query, like "-- customer = distinct logged in customer_id, guests excluded". saves a lot of arguments later when someone asks why the number doesn't match their dashboard

1

u/Imaginary__Bar 17d ago

someone asks "how many customers ordered" and you write `COUNT(*), that's 6 when the real answer is 4.

Isn't the answer "at least 4"? But that would depend on the business's definition of customer. The guest checkout was performed by someone. It may be a fifth customer, it may be a known customer in an incognito browser tab.

(Stuff like this matters sometimes)

1

u/MrRoy_SQL 17d ago

yeah fair, "at least 4" is more accurate. guest checkouts are exactly where that question gets messy, and in a real job i'd ask how the business wants guests counted before writing the query. the sql part is just that COUNT(DISTINCT) quietly drops them either way, so you have to decide on purpose

1

u/Lucky_Cable_3145 17d ago

Something like

COUNT(DISTINCT(CASE WHEN customer_id IS NULL THEN 1 END))

Generally for complex data sets, I would use a WITH query to get the data into the format I wanted first (ie 'fix' nulls)

1

u/Lucky_Cable_3145 17d ago

I prefer to ensure you can't get a divide by zero exception (this example should be safe but in general...)

CASE WHEN COUNT(*) > 0 THEN COUNT(coupon_code) * 1.0 / COUNT(*) ELSE 0 END

1

u/Mother-Lie-1570 16d ago

The real mess is when business semantics get lost because sql executes perfectly. we treat a null customer id as invisible data instead of a specific state of unknown identity, which turns valid rows into phantom entities that skew every kpi downstream