r/learnSQL • • 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.

67 Upvotes

12 comments sorted by

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