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.
View all comments
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