Last lesson gave you one number for the whole table. But nobody asks 'what's revenue?' — they ask 'what's revenue *per country*, *per month*, *per channel*?' That two-word clause, GROUP BY, is the moment SQL becomes an analytics language.
What you'll learn
What
GROUP BY partitions rows into groups that share the same value in one or more columns, then applies aggregate functions to each group separately — one output row per group. HAVING filters those groups after aggregation, the way WHERE filters rows before it.
Why
Per-segment metrics are the daily bread of analytics: revenue by region, signups by week, defect rate by factory. Without GROUP BY you'd run one filtered query per category and paste results together by hand. With it, one query returns the whole breakdown — and HAVING lets you keep only the segments that matter.
Where it's used
Every 'by' in a business question is a GROUP BY: sales by product, churn by cohort, errors by service. It is also the direct SQL ancestor of pandas' df.groupby() you met in the Data Analysis domain.
Where this runs in production
Ops dashboards slice trips by city and hour — GROUP BY city, hour with COUNT(*) and AVG(wait_time) — to spot supply shortages before riders churn.
Content teams rank originals with GROUP BY title_id, country over viewing events, feeding both Top-10 rows and renewal decisions.
Finance reports gross merchandise volume grouped by plan tier and region, with HAVING filters to spotlight segments above materiality thresholds.
Imagine dumping a jar of coins on a table and sorting them into piles by denomination — all quarters here, all dimes there. GROUP BY builds the piles; the aggregate then does something to each pile independently: COUNT counts the coins in it, SUM adds their value. You never get one number for the jar anymore — you get one number per pile.
Once rows are grouped, each output row represents many input rows. So SELECT may only contain: (a) columns you grouped by — they're constant within a group — and (b) aggregates, which summarize the group. Any other bare column is ambiguous (which row's value?) and raises an error like 'column must appear in the GROUP BY clause or be used in an aggregate function'.
SELECT country,
COUNT(*) AS orders,
SUM(amount) AS revenue,
ROUND(AVG(amount), 2) AS avg_order
FROM orders
GROUP BY country;One scan, one row per country, three metrics per row. Grouped column (country) and aggregates only — the single-value rule satisfied. This exact shape — dimension plus metrics — is what BI tools generate under the hood for every bar chart.
WHERE SUM(amount) > 10000 is an error — when WHERE runs, no groups exist yet, so there is nothing to sum. The reverse misuse also hurts: putting a row-level condition in HAVING (HAVING country <> 'US') works mechanically but forces the database to build and aggregate groups it will immediately discard. Row conditions belong in WHERE; aggregate conditions belong in HAVING.
Before trusting a grouped result, predict its row count: GROUP BY country should return about as many rows as COUNT(DISTINCT country). Far more rows than expected usually means you grouped by a near-unique column (like order_id) — each 'group' is one row and the aggregates are meaningless. Groups-of-one is the classic symptom of grouping by the wrong column.
Follow rows left to right: filtered, piled into groups, aggregated, then the piles themselves are filtered. Click each stage for its rules.
How many orders sit in each status?
Rows are piled by status value; COUNT(*) counts each pile. Three distinct statuses in the data → exactly three output rows. status may appear bare in SELECT because it's the grouping column — constant within each pile.
status | orders -----------+-------- shipped | 620 pending | 250 cancelled | 130 (3 rows)
Your task
Build the category report: from order_items (order_item_id, order_id, category, quantity, unit_price), compute per-category revenue (SUM of quantity * unit_price, aliased revenue) and item count (item_count) — but only for non-'misc' categories, and only show categories with revenue over 100. Sort by revenue, highest first. Fill in the blanks.
category | revenue | item_count ----------+---------+------------ audio | 319.00 | 2 (1 row)
Write your solution in the editor on the right, then hit Run.
orders has rows from 6 distinct countries. How many rows does SELECT country, COUNT(*) FROM orders GROUP BY country; return?
What is the key difference between WHERE and HAVING?
Why does SELECT country, status, COUNT(*) FROM orders GROUP BY country; fail?
coupon_code is NULL on 620 of 1,000 orders. What does GROUP BY coupon_code do with those rows?
What is the correct assessment?
From orders (order_id, customer_id, amount, status), find repeat customers: for shipped orders only, return customer_id, order_count, and total_spend for customers with 2 or more shipped orders, sorted by total_spend descending.
Explain the difference between WHERE and HAVING. When would a query use both?
WHERE filters individual rows before any grouping happens — it decides which rows are even eligible to enter a group, and it cannot reference aggregates because none exist yet. HAVING filters whole groups after aggregation — it's the only place an aggregate condition like SUM(amount) > 10000 can live. A query uses both whenever the business question has two filters at different grains: 'countries with over €25k of *shipped* revenue' filters rows by status (WHERE) and groups by total (HAVING). Beyond correctness there's a performance angle: conditions pushed into WHERE shrink the data before the expensive grouping step, while row conditions smuggled into HAVING force the engine to build and aggregate groups it will throw away. My rule of thumb: if the condition mentions an aggregate it must be HAVING; otherwise it belongs in WHERE.
What does the 'column must appear in the GROUP BY clause or be used in an aggregate function' error mean, and how do you decide which fix is right?
It means the SELECT list contains a bare column that isn't a grouping key, so within a group it can hold many different values and the engine has no defensible way to pick one. There are three fixes, and they answer different questions. Adding the column to GROUP BY changes the grain — you now get one row per finer combination, which is right when the column is a real dimension of the question. Wrapping it in an aggregate (MAX(email), MIN(created_at)) keeps the grain and summarizes the column — right when any representative value will do or when it's functionally dependent on the key. Removing it is right when it was never needed. The decision comes from the question's grain: 'revenue per customer' means customer_id is the only grouping key, and anything else in SELECT must be aggregated. I'd also mention the historical MySQL behavior that silently picked an arbitrary value — a reason to be suspicious of old queries migrated from permissive modes.
A grouped revenue report disagrees with the ungrouped total. What are the likely causes you'd check?
First, filter asymmetry: the two queries must share identical WHERE clauses — a status or date filter present in one and not the other is the most common culprit. Second, NULL grouping keys: GROUP BY region includes a NULL group, but if the report drops or hides that row, its revenue silently vanishes from the sum of visible rows; COALESCE(region, 'Unknown') makes it explicit. Third, HAVING: any HAVING threshold removes whole groups after aggregation, so the visible rows genuinely shouldn't sum to the total — the report needs an 'Other' bucket if reconciliation matters. Fourth, join fan-out if the grouped query joins another table: one-to-many joins duplicate order rows and inflate SUMs — I'd verify by comparing COUNT(*) before and after the join. The systematic method is to reconcile stepwise: total → filtered total → grouped totals summed, and find which step introduces the gap.
Common Mistakes to Avoid
1) Selecting a bare column that isn't grouped or aggregated — the single-value rule error. 2) Writing aggregate conditions in WHERE (WHERE COUNT(*) > 5) — aggregates don't exist until after grouping; use HAVING. 3) Sneaking row-level filters into HAVING — works, but pays to aggregate groups you then discard, and muddies intent; put them in WHERE. 4) Forgetting the NULL group — GROUP BY keeps NULL keys as a real group, and hiding that row breaks reconciliation with totals. 5) Grouping by a near-unique column like order_id — every 'group' has one row, aggregates degenerate, and the query silently answers nothing; sanity-check expected group counts with COUNT(DISTINCT key) first.
Ask the AI Tutor
Try these prompts in the AI Tutor panel: • 'ELI5: GROUP BY with a coin-sorting or laundry-sorting analogy of your own.' • 'Quiz me: show small tables and grouped queries — I predict the exact output rows.' • 'Show me a real bug where a filter in HAVING should have been in WHERE.' • 'Walk me through the logical clause order FROM→WHERE→GROUP BY→HAVING→SELECT→ORDER BY with one query.' • 'Interview mode: ask me to write a repeat-customers query and critique my clause placement.'
Glossary
GROUP BY — clause that partitions rows into groups by column values, one output row per group. Group (bucket/pile) — the set of rows sharing the same grouping-key values. Grouping key — the column(s) listed in GROUP BY. Grain — the level of detail of a result: what one row represents (per country, per customer-month…). Single-value rule — SELECT may contain only grouping columns and aggregates. HAVING — post-aggregation filter on groups; may reference aggregates. Logical evaluation order — FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. NULL group — the single group formed by rows whose grouping key is NULL. Fan-out — row duplication from one-to-many joins that inflates grouped sums. Dimension — a categorical column you group by; Metric — an aggregated value you compute per dimension.
Recommended Resources
• Docs: PostgreSQL manual — 'GROUP BY and HAVING clauses' (and peek at GROUPING SETS/ROLLUP for where this road leads). • Read: Mode's SQL tutorial chapters on GROUP BY and HAVING for stakeholder-flavored practice. • Practice: take last lesson's profiling battery and re-run it grouped — one row per category instead of one row per table; then add a HAVING to keep only categories above a threshold. • Next in DSM: grouping summarizes one table — INNER JOIN, first lesson of the Joins module, teaches you to combine two.
Recap
✓ GROUP BY partitions rows by column values and runs aggregates once per group — one output row per group, NULL keys included as their own group. ✓ Multiple grouping columns create one group per distinct value combination; predict row counts with COUNT(DISTINCT …). ✓ The single-value rule: SELECT may contain only grouping columns and aggregates. ✓ WHERE filters rows before grouping and can't see aggregates; HAVING filters groups after aggregation and exists for aggregate conditions. ✓ The logical order — FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT — explains every visibility rule. ✓ Top-segment questions combine the whole toolkit: filter, group, aggregate, filter groups, rank, cut. Next up: INNER JOIN. Every query so far lived in one table — but customers live in one table and their orders in another. The Joins module starts with the join type that powers 90% of real queries.
Run your code to see the output here.