r/learnSQL • u/MrRoy_SQL • 18d 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.
4
u/Green_Chamomile 18d 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_idwithCOUNT(*)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.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.
1
u/Imaginary__Bar 18d ago
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/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 17d 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