{"course":{"slug":"data-and-sql","title":"Data, SQL and Getting to the Answer","url":"https://addaly.com/learn/data-and-sql","level":"beginner","track":"build"},"lesson":{"slug":"group-by","title":"GROUP BY: making piles and asking about each one","url":"https://addaly.com/learn/data-and-sql/group-by","position":23,"of":66,"minutes":7,"answer":"Each aggregate answers a question about one pile, and COUNT(*) and COUNT(DISTINCT) are different questions.","keyIdea":"Each aggregate answers a question about one pile, and COUNT(*) and COUNT(DISTINCT) are different questions."},"body":{"format":"markdown","text":"## Piles\n\n`GROUP BY city` takes all your rows and sorts them into piles, one pile per distinct city. Then every aggregate you write (`COUNT`, `SUM`, `AVG`, `MAX`) is computed **inside one pile**, and you get **one output row per pile**.\n\n```sql\nSELECT   city, COUNT(*) AS orders, SUM(amount) AS revenue\nFROM     orders\nWHERE    placed_at >= '2026-03-01' AND placed_at < '2026-04-01'\nGROUP BY city\nORDER BY revenue DESC;\n```\n\nThirty thousand rows go in. Fourteen rows come out, one per city. That is the whole idea.\n\n## Why the database refuses your query\n\nAdd a column and it breaks:\n\n```sql\nSELECT city, customer_name, COUNT(*)\nFROM orders\nGROUP BY city;\n-- ERROR: column \"orders.customer_name\" must appear in the GROUP BY clause\n--        or be used in an aggregate function\n```\n\nThe error looks bureaucratic. It is not. The Lagos pile contains 900 rows with 900 different customer names, and the output has exactly one slot for Lagos. Which of the 900 should go there? There is no right answer, so the database refuses to invent one.\n\nEvery column in your SELECT must be either something you grouped by (so it is the same for the whole pile) or wrapped in an aggregate (so the pile is squeezed into a single value). MySQL in some configurations will let this slide and pick a name at random. That is worse, not better.\n\n## The three counts\n\nThese are different questions and they routinely get confused.\n\n`COUNT(*)` counts rows in the pile. Full stop.\n\n`COUNT(phone)` counts rows in the pile where `phone` is not NULL. Aggregates skip NULLs, always.\n\n`COUNT(DISTINCT customer_id)` counts how many different customers appear in the pile.\n\nSo in a table where one row is one order:\n\n- `COUNT(*)` is **how many orders**\n- `COUNT(DISTINCT customer_id)` is **how many customers ordered**\n\nNeither is more correct. They answer different questions, and the whole skill is knowing which question you were asked. A repeat customer with four orders is one customer and four orders, and both facts are true at the same time.\n\n## WHERE before, HAVING after\n\n`WHERE` filters rows on their way into the piles. `HAVING` filters whole piles on their way out.\n\n```sql\nSELECT   city, SUM(amount) AS revenue\nFROM     orders\nWHERE    status = 'paid'          -- discard rows before piling\nGROUP BY city\nHAVING   SUM(amount) > 500000     -- discard piles after summing\nORDER BY revenue DESC;\n```\n\nYou cannot put `SUM(amount) > 500000` in WHERE, because when WHERE runs the piles do not exist yet. You *can* put `status = 'paid'` in HAVING in some databases, but do not: it means the same thing, runs later, and reads as though it is a statement about the city rather than about individual orders.\n\n## Two things worth knowing early\n\n**No GROUP BY means one pile containing everything.** `SELECT COUNT(*) FROM orders` is a group query with a single group. That is why you get one row back.\n\n**Grouping by two columns makes a pile per combination.** `GROUP BY city, month` gives one row per city per month. If you have 14 cities and 12 months, expect up to 168 rows, and expect fewer, because combinations with no rows produce no pile at all. A city with zero orders in July does not appear as a zero; it is not there. If your report needs that zero, you have to supply the list of months yourself and join to it. Missing rows are much harder to notice than wrong numbers."},"check":{"question":"In a table with one row per order, `COUNT(*)` for March returns 812 and `COUNT(DISTINCT customer_id)` returns 540. A colleague says the gap means the data needs cleaning. What is actually going on?","options":[{"text":"The 812 is inflated by duplicate rows, and the duplicates should be removed before either number is reported","correct":false,"tempting":"Reads a repeated customer id as a data-quality defect rather than as the normal shape of repeat business."},{"text":"COUNT(DISTINCT ...) is the safer number and should be preferred whenever the two disagree","correct":false,"tempting":"Turns a choice between two questions into a rule about which function is more trustworthy."},{"text":"Each row is one order, so a customer who ordered several times is counted once per order; both numbers are correct and answer different questions","correct":true},{"text":"The WHERE clause ran before the grouping, so rows were counted that the grouping would have excluded","correct":false,"tempting":"Blames execution order for a gap that has nothing to do with filtering."}]},"prev":{"slug":"a-spine-to-join-against","title":"Generating the rows that are not there","url":"https://addaly.com/learn/data-and-sql/a-spine-to-join-against"},"next":{"slug":"null","title":"NULL, and why your counts are wrong","url":"https://addaly.com/learn/data-and-sql/null"},"authorship":"Founder and series editor: Dr Mustafa Mun. Written by AI models to a written standard. Exam keys checked blind by a second model: 3,665 of 3,666 agreed. Academic review invited.","review":{"status":"Draft, open for academic review"},"cite":"Addaly (2026) 'GROUP BY: making piles and asking about each one', in Data, SQL and Getting to the Answer. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/data-and-sql/group-by (Accessed: 31 October 2026).","licence":{"name":"CC BY-NC-SA 4.0","url":"https://creativecommons.org/licenses/by-nc-sa/4.0/"},"reader":{"schema":1,"title":"GROUP BY: making piles and asking about each one","courseTitle":"Data, SQL and Getting to the Answer","module":{"index":3,"slug":"summaries-and-the-piles-behind-them","title":"Summaries, and the piles behind them"},"answer":null,"keyIdea":"Each aggregate answers a question about one pile, and COUNT(*) and COUNT(DISTINCT) are different questions.","hero":{"kind":"plate","image":null,"alt":null,"caption":null},"blocks":[{"t":"h2","id":"piles","runs":[{"text":"Piles"}]},{"t":"p","runs":[{"text":"GROUP BY city","code":true},{"text":" takes all your rows and sorts them into piles, one pile per distinct city. Then every aggregate you write ("},{"text":"COUNT","code":true},{"text":", "},{"text":"SUM","code":true},{"text":", "},{"text":"AVG","code":true},{"text":", "},{"text":"MAX","code":true},{"text":") is computed "},{"text":"inside one pile","b":true},{"text":", and you get "},{"text":"one output row per pile","b":true},{"text":"."}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT   city, COUNT(*) AS orders, SUM(amount) AS revenue\nFROM     orders\nWHERE    placed_at >= '2026-03-01' AND placed_at < '2026-04-01'\nGROUP BY city\nORDER BY revenue DESC;"},{"t":"p","runs":[{"text":"Thirty thousand rows go in. Fourteen rows come out, one per city. That is the whole idea."}]},{"t":"h2","id":"why-the-database-refuses-your-query","runs":[{"text":"Why the database refuses your query"}]},{"t":"p","runs":[{"text":"Add a column and it breaks:"}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT city, customer_name, COUNT(*)\nFROM orders\nGROUP BY city;\n-- ERROR: column \"orders.customer_name\" must appear in the GROUP BY clause\n--        or be used in an aggregate function"},{"t":"p","runs":[{"text":"The error looks bureaucratic. It is not. The Lagos pile contains 900 rows with 900 different customer names, and the output has exactly one slot for Lagos. Which of the 900 should go there? There is no right answer, so the database refuses to invent one."}]},{"t":"p","runs":[{"text":"Every column in your SELECT must be either something you grouped by (so it is the same for the whole pile) or wrapped in an aggregate (so the pile is squeezed into a single value). MySQL in some configurations will let this slide and pick a name at random. That is worse, not better."}]},{"t":"h2","id":"the-three-counts","runs":[{"text":"The three counts"}]},{"t":"p","runs":[{"text":"These are different questions and they routinely get confused."}]},{"t":"p","runs":[{"text":"COUNT(*)","code":true},{"text":" counts rows in the pile. Full stop."}]},{"t":"p","runs":[{"text":"COUNT(phone)","code":true},{"text":" counts rows in the pile where "},{"text":"phone","code":true},{"text":" is not NULL. Aggregates skip NULLs, always."}]},{"t":"p","runs":[{"text":"COUNT(DISTINCT customer_id)","code":true},{"text":" counts how many different customers appear in the pile."}]},{"t":"p","runs":[{"text":"So in a table where one row is one order:"}]},{"t":"ul","items":[[{"text":"COUNT(*)","code":true},{"text":" is "},{"text":"how many orders","b":true}],[{"text":"COUNT(DISTINCT customer_id)","code":true},{"text":" is "},{"text":"how many customers ordered","b":true}]]},{"t":"p","runs":[{"text":"Neither is more correct. They answer different questions, and the whole skill is knowing which question you were asked. A repeat customer with four orders is one customer and four orders, and both facts are true at the same time."}]},{"t":"h2","id":"where-before-having-after","runs":[{"text":"WHERE before, HAVING after"}]},{"t":"p","runs":[{"text":"WHERE","code":true},{"text":" filters rows on their way into the piles. "},{"text":"HAVING","code":true},{"text":" filters whole piles on their way out."}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT   city, SUM(amount) AS revenue\nFROM     orders\nWHERE    status = 'paid'          -- discard rows before piling\nGROUP BY city\nHAVING   SUM(amount) > 500000     -- discard piles after summing\nORDER BY revenue DESC;"},{"t":"p","runs":[{"text":"You cannot put "},{"text":"SUM(amount) > 500000","code":true},{"text":" in WHERE, because when WHERE runs the piles do not exist yet. You "},{"text":"can","i":true},{"text":" put "},{"text":"status = 'paid'","code":true},{"text":" in HAVING in some databases, but do not: it means the same thing, runs later, and reads as though it is a statement about the city rather than about individual orders."}]},{"t":"h2","id":"two-things-worth-knowing-early","runs":[{"text":"Two things worth knowing early"}]},{"t":"p","runs":[{"text":"No GROUP BY means one pile containing everything.","b":true},{"text":" "},{"text":"SELECT COUNT(*) FROM orders","code":true},{"text":" is a group query with a single group. That is why you get one row back."}]},{"t":"p","runs":[{"text":"Grouping by two columns makes a pile per combination.","b":true},{"text":" "},{"text":"GROUP BY city, month","code":true},{"text":" gives one row per city per month. If you have 14 cities and 12 months, expect up to 168 rows, and expect fewer, because combinations with no rows produce no pile at all. A city with zero orders in July does not appear as a zero; it is not there. If your report needs that zero, you have to supply the list of months yourself and join to it. Missing rows are much harder to notice than wrong numbers."}]}],"check":{"question":[{"text":"In a table with one row per order, "},{"text":"COUNT(*)","code":true},{"text":" for March returns 812 and "},{"text":"COUNT(DISTINCT customer_id)","code":true},{"text":" returns 540. A colleague says the gap means the data needs cleaning. What is actually going on?"}],"options":[{"text":[{"text":"The 812 is inflated by duplicate rows, and the duplicates should be removed before either number is reported"}],"correct":false,"tempting":[{"text":"Reads a repeated customer id as a data-quality defect rather than as the normal shape of repeat business."}]},{"text":[{"text":"COUNT(DISTINCT ...) is the safer number and should be preferred whenever the two disagree"}],"correct":false,"tempting":[{"text":"Turns a choice between two questions into a rule about which function is more trustworthy."}]},{"text":[{"text":"Each row is one order, so a customer who ordered several times is counted once per order; both numbers are correct and answer different questions"}],"correct":true,"tempting":null},{"text":[{"text":"The WHERE clause ran before the grouping, so rows were counted that the grouping would have excluded"}],"correct":false,"tempting":[{"text":"Blames execution order for a gap that has nothing to do with filtering."}]}],"order":[1,0,2,3],"why":null},"prev":{"slug":"a-spine-to-join-against","title":"Generating the rows that are not there","minutes":8},"next":{"slug":"null","title":"NULL, and why your counts are wrong","minutes":8}}}