Lesson 23 / 37 · Grouping

count(*), count(col) and count(DISTINCT col) count different things

Order 108 is paid but has no amount. count(*) counts it, count(amount) does not, and sum(amount) quietly ignores it. count(DISTINCT customer_id) counts each customer once, however many orders they placed. The aggregate step says how many NULLs each function skipped.

The query

SELECT status, count(*) AS all_rows, count(amount) AS with_amount,
       count(DISTINCT customer_id) AS customers, sum(amount) AS total
FROM orders
GROUP BY status

Try count(DISTINCT amount): for paid it gives 9, not 11, because three paid orders share 189.

Next: WHERE filters rows, HAVING filters groups · All lessons