In the previous lesson you got a single figure for the whole company: €727.95 of revenue. It's a fact, but it isn't an analysis. What management really asks isn't "how much have we sold?" but "how much have we sold of each thing?": by category, by customer, by country, by month, by sales rep. That word —by— is the unmistakable sign that it's time for GROUP BY.

GROUP BY splits the set of rows into groups and applies the aggregate to each group separately, returning one row per group instead of one for everything. It is, without exaggeration, the clause that turns SQL into an analysis tool. In this lesson you'll learn to group by one column, by several and by an expression; you'll finally extend the logical execution order diagram with GROUP BY and HAVING —we promised it in 02-01—; you'll see why NULLs form a group of their own; you'll master the central pattern of analysis, which is GROUP BY combined with JOIN; and you'll discover why GreenStore's category 6 disappears from your reports and what to do to make it appear with an honest 0.

1. GROUP BY: splitting into groups and aggregating each one
2. The logical execution order, extended
3. The golden rule: grouped or aggregated
4. Grouping by one column
5. Grouping by several columns
6. Grouping by an expression
7. GROUP BY and the SELECT's alias
8. Groups and NULL
9. GROUP BY with JOIN: the central pattern of analysis
10. Empty groups: why category 6 doesn't show up
11. Sorting by the aggregate and keeping the top N
12. ROLLUP, GROUPING SETS and CUBE
13. Common Mistakes and Tips
14. Exercises
15. Conclusion

The idea is visual. With no GROUP BY, every row goes into a single pile. With GROUP BY, they're dealt into piles according to a column's value, and the aggregate is computed within each pile:

And the query:

5 rows, one per status. Compare that with what you'd have had to do without GROUP BY: five queries with WHERE status = '…', or one with five COUNT(*) FILTER (...). And if a sixth status appeared tomorrow, GROUP BY would show it on its own, whereas the other two versions would have to be rewritten.

The general principle: GROUP BY produces one row per distinct combination of values of the grouped columns. The result's row count is exactly the number of distinct values, not one more and not one less.

Since 02-01 you've been building a diagram of the order in which SQL understands a query. Module 2 left it at five steps and module 3 showed that JOINs happen inside the FROM. Now we insert the two missing pieces, between WHERE and SELECT:

This diagram isn't decoration: on its own it explains nearly everything that comes next. Three immediate consequences:

That last one has an important nuance in PostgreSQL, and we devote the whole of section 7 to it.

The reasoning to keep in mind always: once GROUP BY runs, individual rows stop existing as such. From step 3 onwards, the working set is no longer 47 order lines: it's 5 groups. Everything you write from there on has to make sense at group level.

It's a single sentence, and everything else follows from it:

Every expression in the SELECT must be (a) in the GROUP BY, or (b) inside an aggregate function. No exceptions.

Watching it fail is the best way to understand it:

And the engine is right. The "Food" group's row represents five products: the oil, the rice, the honey, the pasta and the tomato. Which of the five names should appear in that cell? There's no possible answer, so PostgreSQL refuses to invent one.

The three valid ways out:

Option b is especially useful, and it shows the rule isn't a whim:

6 rows, one per category, with the 20 products spread out: 5 + 4 + 4 + 4 + 2 + 1 = 20. STRING_AGG has answered "which ones?" without breaking the rule, because it's an aggregate.

PostgreSQL is a bit cleverer than the statement suggests: if you group by a table's primary key, it lets you select any other column from that same table, because the PK functionally determines the rest of the values.

It works because cat.id is a PRIMARY KEY: within a group with the same id there can only be one name, so there's no ambiguity. It's a very practical convenience —it saves you having to repeat five columns in the GROUP BY— and it's been part of the SQL standard since 1999.

Dialect note — and why MySQL's permissiveness is a trap.

Engine	Does it allow a bare column with an aggregate?	What it returns
PostgreSQL	No (except for functional dependency on the PK)	An explicit error
MySQL with ONLY_FULL_GROUP_BY (default since 5.7.5)	No	An explicit error
MySQL with ONLY_FULL_GROUP_BY off	Yes	An arbitrary value from any row in the group
SQLite	Yes, always	An arbitrary value (with the documented exception of MIN/MAX, where it returns that row's value)
SQL Server	No	An explicit error
Oracle	No	An explicit error

Permissive MySQL is the trap: the query doesn't fail, and in tests with little data it even seems to return "the first one", which is usually the one you expected. In production, with a different execution plan, it returns another. A report that said "Food — Extra virgin olive oil — 5 products" starts saying "Food — Organic crushed tomato — 5 products" without anybody having touched anything. If you work with MySQL, check that ONLY_FULL_GROUP_BY is on and don't turn it off.

4 rows adding up to 20 orders and €118.25 of shipping: the same master figures from 04-04, now broken down. And a story is already readable: card dominates (11 out of 20) and cash on delivery is the method with the most expensive shipping (€9.50 on average), which makes sense because those are the most distant deliveries.

3 rows. Notice COUNT(DISTINCT city): 11 Spanish customers spread across only 7 cities, because Valencia holds four and Barcelona two.

6 rows, the 6 categories, because we're grouping the products table and every product has a category. Hold on to that detail: in section 10 you'll see that grouping the sales by category gives only 5 rows, and understanding why is the difference between an honest report and an incomplete one.

Note what happens with category 6: a single product, so AVG, MIN and MAX coincide. Aggregates over a one-row group return that same value.

When you list several columns in the GROUP BY, the engine forms one group per distinct combination of values.

8 rows. Watch out for an important point: it is not 3 countries × 5 statuses = 15 rows. GROUP BY produces one row per combination that exists in the data, not per possible combination. There's no cancelled French order, so that row doesn't appear — it doesn't appear with a zero, it simply doesn't exist. (If you needed the complete grid, empty combinations included, the route would be 03-06's CROSS JOIN with a LEFT JOIN on top.)

The order of the columns in the GROUP BY doesn't change the result, only the mental reading. GROUP BY c.country, o.status and GROUP BY o.status, c.country return the same 8 groups. What does change the report's look is the ORDER BY.

And the classic analysis case: sales by category and year.

9 rows, not 10: Sustainable home sold nothing in 2026, so that combination doesn't exist. It's the same phenomenon as before, and in a trend report it's exactly the kind of gap you have to know how to read.

(EXTRACT(YEAR FROM …) pulls the year out of a date. Date functions are studied in depth in 06-03; here it's just an instrument.)

You aren't limited to columns: you can group by any expression computed from them. It's what you've just done with EXTRACT, and it's what lets you build bands, cohorts and segmentations.

Let's group the catalogue by price band:

4 bands adding up to the 20 products. GreenStore's catalogue is clearly skewed towards cheap products: 13 of the 20 references cost less than €10.

Two things about this query deserve comment:

1. The expression appears twice, identically, in the SELECT and in the GROUP BY. It's ugly and it's necessary, for the usual reason: the alias price_range doesn't exist yet when the GROUP BY runs. (Except in PostgreSQL, which makes a concession — section 7.) The ways of avoiding the repetition are module 7's subqueries and module 10's CTEs.
2. The numeric prefix on the labels (1 · , 2 · …) isn't decorative. ORDER BY price_range sorts alphabetically, and without the prefix the order would be "€10 to €15", "€15 or more", "€5 to €10", "under €5": a mess. It's a common trick when building bands.

CASE is studied in depth in 06-05; here it's used as a classification tool.

This point is explained badly in a lot of places, so let's be precise.

By the logical order, the GROUP BY (step 3) runs before the SELECT (step 5), where aliases are born. Therefore the SQL standard doesn't allow using a SELECT alias in the GROUP BY.

In practice, PostgreSQL makes a concession: it accepts a GROUP BY element being the name of an output column (an alias) or its ordinal number. It's an engine extension, documented and very convenient:

It also works by ordinal number, GROUP BY 1, though that form is fragile: if somebody adds a column at the start of the SELECT, the 1 starts referring to something else.

Now the three limitations of that concession, which are what produce the confusing errors.

As soon as the alias goes into a larger expression, PostgreSQL stops resolving it as an output name and looks for it as a table column, where it doesn't exist.

This is the dangerous one. If a SELECT alias clashes with the name of a real column in the table, PostgreSQL uses the table's column, not your alias:

The message looks absurd —"but I wrote GROUP BY category_id, which is exactly the alias of supplier_id"— until you understand the rule: the GROUP BY resolved category_id as products.category_id, the real column. And then supplier_id is left bare in the SELECT, which is exactly what the error is reporting. An alias that shadows a column name is always a bad idea.

PostgreSQL's concession covers GROUP BY and ORDER BY, but not HAVING. There you have to repeat the whole aggregate: HAVING COUNT(*) > 3. Lesson 04-06 develops it.

The course's recommendation: even though PostgreSQL allows it, repeat the full expression in the GROUP BY. It's longer, yes, but it's portable, it doesn't depend on name-resolution rules and it works the same way in all four clauses. Save aliases for the ORDER BY, where they're standard and hold no surprises.

In 04-03 you saw the table of where NULLs are considered equal to each other, and GROUP BY was on the list. Here it is in action, and it's one of the most useful things in the whole lesson:

4 groups, and the fourth is the NULL one: 10 web-channel orders with €46.10 of shipping. GROUP BY has bundled the ten nulls into a single group, even though NULL = NULL is UNKNOWN.

And this is exactly what you want: the web channel is a real business category and it deserves its row. Compare it with what would have happened if the design had used a sentinel (employee_id = 0): the group would exist all the same, but it would also sneak into COUNT(DISTINCT employee_id) as if it were a genuine sales rep, giving 4 instead of 3.

For the report to read well, the null row needs a label. COALESCE (06-04) or CASE (06-05) solves it; for now, the ORDER BY's NULLS LAST (02-05) at least puts it where it belongs.

The same with referrals:

7 rows. The null group (7 customers who arrived on their own) is the largest, and Lucía Martínez Soler (id 1) is the best advocate with 3 referrals. That's an actionable fact no previous query in the course could give.

Now we reach what actually gets done every day in any company. 03-02's canonical four-table query is still the basis; all that changes is that we now put a GROUP BY on top of it.

These are GreenStore's revenue figures by category. They add up to €727.95, the total from 04-04. And they already tell a commercial story: Food leads in revenue and in units (49 of 113), while Natural cosmetics bills €156.32 on only 16 units — its value per unit is almost five times higher.

Five rows, not six. Supplements is missing. We'll come back to that in section 10.

12 rows —the 12 customers who have bought— and GreenStore's customer ranking: Sofia Moreira Costa leads with €111.88, followed very closely by Lucía Martínez Soler with €107.60 spread across three orders.

Two technical details worth seeing:

• COUNT(DISTINCT o.id) is essential. COUNT(*) counts lines, not orders: Lucía has 9 lines across 3 orders. It's 03-02's row multiplication, and DISTINCT is how you undo it when counting.
• Every non-aggregated column is in the GROUP BY. Since c.id is customers' PK, PostgreSQL would let us write just GROUP BY c.id; they've all been listed so the query is portable.

And the warning, now resolved: if you added SUM(o.shipping_cost) to this query you'd get an inflated number, because each order appears as many times as it has lines. Lucía would pay her shipping nine times. It's exactly the problem from 04-04 section 11, and the solution is the same: aggregate the shipping in a separate query (or, from module 7 onwards, with a subquery that collapses the lines before joining).

(First 6 of 17 rows.)

17 rows in total, not 20: products 13, 19 and 20 have never been sold and don't appear. And look at the contrast between the two figures: the rice and the tomato tie on units (14), but the rice bills more than double because it costs twice as much. The best-selling product by units and the highest-billing one are almost never the same, and that's why a sales report needs both metrics.

Go back to section 9.1: five categories in the sales report, six in the catalogue. Supplements has vanished.

It isn't a GROUP BY fault. It's 03-02's INNER JOIN doing what it does: discarding what doesn't match. The only Supplements product (the spirulina) doesn't appear in order_lines, so no row of the FROM belongs to that category, and a group with no rows doesn't exist.

GROUP BY doesn't invent groups: it only deals out the rows it's given.

6 rows. There's Supplements, with the truth: 0 lines, 0 units and a null revenue.

Three decisions in that query deserve explaining, and they're the most important part of the section:

1. COUNT(ol.id) and not COUNT(*). This is critical. The Supplements row exists in the LEFT JOIN's result —with all of order_lines' columns at NULL— so COUNT(*) would count it and return 1, not 0. COUNT(ol.id) counts only the non-null values and returns the honest 0.

It's 04-04 section 3's rule applied to the case that matters most. In any GROUP BY over a LEFT JOIN, always count a column from the right-hand table, never *.

2. The revenue comes out as *(null)* and not 0.00. Because SUM of an empty set is NULL (04-04, section 7). To present it as 0.00 you'd need COALESCE(SUM(...), 0), which is what we've done with the units. COALESCE is 06-04; here it's been used once so you can see the contrast between the two columns.

3. Both JOINs are LEFT. Remember 03-03 section 7's rule: an INNER JOIN after a LEFT JOIN cancels its effect. If the second hop were JOIN order_lines, Supplements would disappear again.

3 rows, the same three never-sold products you found with 03-03's anti-join, now with the diagnosis alongside: no stock, a commercial problem, discontinued. (The HAVING is the next lesson; it appears here in passing because it's the natural way of filtering by a count.)

The rule to take away: an INNER JOIN answers "how much has each category that has sold something sold?"; a LEFT JOIN answers "how much has each category sold?". The second is almost always the business question, because a zero is information too: it tells management that Supplements isn't working. A report that hides the zeros hides precisely the problems.

ORDER BY runs after the SELECT (step 6 of the diagram), so it can sort by an aggregate or by its alias without a problem:

GreenStore's top 5 products by revenue. The olive oil is the star product with €109.53, 15 % of all revenue.

Notice two things:

• The ORDER BY ends with p.id, a unique column, as the course's convention has demanded since 02-05. Without it, two products with the same revenue could come out in a different order on each run and pagination would be unstable.
• You can also sort by an aggregate that isn't in the SELECT: ORDER BY SUM(ol.quantity) DESC is perfectly legal even if you don't display the units. It's legal and sometimes confusing, so use it carefully.

The combination GROUP BY + ORDER BY aggregate DESC + LIMIT n is the top N pattern, and it's probably the most requested analytical query there is: the 10 biggest-spending customers, the 5 slowest-moving products, the 3 sales reps with the most sales.

Note: if what you want is a top N within each group —"the 3 best-selling products of each category"— GROUP BY and LIMIT aren't enough: you need a window function (ROW_NUMBER() OVER (PARTITION BY ...)), and that's module 10.

A real report nearly always needs subtotals and a grand total alongside the detail. Writing that with a UNION ALL of several queries is tedious and slow, because it forces the table to be traversed several times. SQL offers three GROUP BY extensions to solve it in a single pass:

A minimal example with ROLLUP, over section 5's sales by category and year:

15 rows: the 9 real combinations, 5 subtotals by category and the grand total of €727.95 in the last one. The subtotal rows are recognisable because the lower-level grouped columns are NULL.

And there's ROLLUP's only complication: those subtotal NULLs are indistinguishable from a data NULL. The GROUPING(column) function exists to tell them apart (it returns 1 if the row is a subtotal for that column), and combined with CASE (06-05) it lets you label the rows as "Category total" or "GRAND TOTAL".

We won't go deeper: ROLLUP and company are used a lot in management reports and in business intelligence tools, but their natural place is after you've mastered CASE and subqueries.

Dialect note: ROLLUP, CUBE and GROUPING SETS exist in PostgreSQL 9.5+, SQL Server and Oracle. MySQL only has GROUP BY ... WITH ROLLUP (a different syntax and no CUBE or GROUPING SETS). SQLite has none of them: you have to emulate them with UNION ALL.

• Putting a column in the SELECT that's neither grouped nor aggregated. column ... must appear in the GROUP BY clause. It's the most frequent error of the lesson, and in permissive MySQL or in SQLite it raises no error: it returns an arbitrary value.
• Using COUNT(*) in a GROUP BY over a LEFT JOIN. It returns 1 where it should return 0. Count a column from the right-hand table: COUNT(ol.id).
• Expecting empty groups to appear with an INNER JOIN. Category 6 doesn't come out. If the report has to list every category, start from categories with a LEFT JOIN.
• Slipping an INNER JOIN in after the LEFT JOIN in the same chain. It cancels the LEFT and the empty groups disappear again (03-03).
• Summing a header column after joining with the detail. It's still 04-04's mistake: Lucía's shipping would be counted nine times.
• Counting orders with COUNT(*) in a query that starts from order_lines. It counts lines. Use COUNT(DISTINCT o.id).
• Using a SELECT alias in the HAVING. column "..." does not exist in PostgreSQL. Repeat the aggregate.
• Assuming GROUP BY generates every possible combination. It only generates the ones present in the data: 8 country × status rows, not 15.
• Sorting bands alphabetically with no numeric prefix. "€10 to €15" comes before "€5 to €10". Number the labels.
• Forgetting the unique column at the end of the ORDER BY. With ties, the order stops being deterministic.
• Tip: write the query without aggregating first and look at the rows. If the detail isn't what you expect, the aggregate you put on top will be wrong even if it compiles.
• Tip: check that the groups add up to the total. 256.27 + 195.28 + 156.32 + 88.58 + 31.50 = €727.95. If it doesn't reconcile with the overall figure, there are rows lost or duplicated.
• Tip: count the groups you expect before running it. If you group by status there should be at most 5 rows (the CHECK's domain); if 6 come out, there's an unexpected value in the data.

Logistics wants an activity-by-sales-rep report. Write a query over orders and employees that returns, for all 8 employees (whether or not they appear in orders): their id, full name, job title, number of orders handled and the sum of the shipping costs of those orders.

Sort by number of orders descending. Then answer:

1. How many rows does it return and why?
2. What would have happened with an INNER JOIN?
3. Why can't the web channel appear here?

Marketing wants to segment the catalogue by supplier. Write a query that returns, for each supplier: their name, their country, whether they're active, how many products they supply, the average price of those products (two decimals) and the units sold of all of them.

The inactive supplier must appear too. Then answer: how many units has EcoNordic Supplies sold despite being inactive, and what does that tell you about the business?

Management wants the sales-by-month report for the whole history, with these columns: month (in YYYY-MM format), number of distinct orders, number of lines, units and revenue.

1. Write it. (Hint: you can group by the expression TO_CHAR(o.order_date, 'YYYY-MM'), a string function you'll see in depth in 06-03.)
2. Identify the best month and the worst.
3. Explain why the sum of the monthly revenues must give €727.95 and check it.

Solution 1

1. 8 rows, the 8 employees. The LEFT JOIN from employees keeps the five who have never handled an order, and COUNT(o.id) gives them a correct 0 (with COUNT(*) they'd have come out with 1). COALESCE turns SUM's NULL into a presentable 0.00.

2. With an INNER JOIN there'd be 3 rows: only Óscar, Laia and Marc. Rosa, Andrés, Beatriz, Irene and Daniel would disappear — which is exactly what happened in 03-04, where you met them as "the employees who have never handled an order". An HR report with 3 people out of 8 isn't a report.

3. The web channel can't appear because those 10 orders have employee_id at NULL and match no row of employees. Starting from employees with a LEFT JOIN, those rows sit on the right-hand side with no partner and are discarded: the sum of the orders column gives 10, not 20. To see them you'd have to start from orders (GROUP BY employee_id, section 8) or use a FULL OUTER JOIN (03-05). It's the LEFT JOIN's asymmetry in its purest form: it decides which side comes out whole, and the other loses its orphans.

Solution 2

5 rows, the five suppliers, with the 20 products spread out: 5 + 3 + 4 + 4 + 4 = 20. Two important details of this query:

• COUNT(DISTINCT p.id) and not COUNT(p.id). After the second LEFT JOIN, each product appears as many times as it has been sold: the oil would be there five times. COUNT(p.id) would give 16 for Huerta del Turia instead of 5. It's 03-02's row multiplication, and DISTINCT is the correction.
• AVG(p.price) is affected too, and that one can't be fixed with DISTINCT. Huerta del Turia's average price comes out as €6.73, not the simple mean of its five products (which is €5.74), because the mean is weighted by the number of times each reference has been sold: the €12.50 oil goes in five times and the €1.95 tomato only twice. It's a legitimate average —"average price of what gets billed"— but it isn't the one the exercise asked for. For the catalogue's average price you have to compute it in a separate query over products, or with a subquery (module 7). It's exactly 04-04's granularity trap, now over an average instead of a sum, and it's more insidious because the resulting number looks plausible.

EcoNordic Supplies has sold 12 units despite being inactive, because suppliers.active = false means "we don't buy from them any more", not "their products disappear from the catalogue". Its four references are the detergent (10), the candles (13), the bamboo toothbrush (18) and the spirulina (20); two of them have never been sold, but the bamboo toothbrush has shifted 9 units and the detergent 3. The actionable fact is clear: there are two products selling normally whose supplier is no longer operational. That's a supply problem just around the corner, and this query is exactly the one that detects it.

Solution 3

2. The best month is October 2025 with €97.20, followed very closely by June 2025 with €95.48. The worst is November 2025 with €31.70, a month with a single order. With volumes this small, a good or bad month depends on whether a big order happened to land, and that's an analytical lesson in itself: with 20 orders you can't talk about seasonality.

3. The sum must give €727.95 because each of the 47 lines belongs to exactly one order, each order has exactly one date, and each date belongs to exactly one month. The groups are a partition of the set of lines: they don't overlap and they leave nothing out. The check:

And the line count: 5+5+5+5+3+2+2+5+3+5+4+3 = 47. Both reconcile.

This check —do the groups add up to the total?— is the best validation there is for an aggregate query, and it deserves to become a reflex. If it doesn't reconcile, either you've lost rows (an INNER JOIN discarding) or you've duplicated them (a JOIN multiplying).

GROUP BY is the clause that turns SQL into analysis:

• It splits the rows into groups and applies the aggregate to each one, returning one row per distinct combination that exists in the data — never for combinations that don't.
• The logical execution order is now complete: FROM/JOIN → WHERE (rows) → GROUP BY (groups) → HAVING (groups) → SELECT (aliases) → DISTINCT → ORDER BY → LIMIT. From that follows why WHERE can't use aggregates and HAVING can.
• The golden rule: every column in the SELECT is grouped or aggregated. PostgreSQL, SQL Server and Oracle demand it; MySQL with ONLY_FULL_GROUP_BY off and SQLite return an arbitrary value, and that's a trap, not a convenience.
• You can group by one column, by several (one row per existing combination) and by an expression (EXTRACT, CASE, TO_CHAR), repeating it in full in the GROUP BY.
• PostgreSQL accepts a SELECT alias in the GROUP BY as an extension, but only bare, with the real column winning in case of ambiguity, and never in HAVING. Repeating the expression is always safer.
• NULLs form a group of their own: the 10 web-channel orders and the 7 walk-in customers appear as a row with *(null)*, and that row is valuable information.
• GROUP BY with JOIN is the central pattern of analysis. You now have GreenStore's key figures: revenue by category (Food €256.27 · Drinks €195.28 · Natural cosmetics €156.32 · Sustainable home €88.58 · Personal hygiene €31.50), the customer ranking (Sofia €111.88 · Lucía €107.60) and the product top (oil €109.53 · matcha €88.00).
• Empty groups don't exist for an INNER JOIN. Category 6 only appears with a LEFT JOIN from categories and COUNT(ol.id) instead of COUNT(*), which is what turns it into an honest 0 rather than a false 1.
• The top N pattern is GROUP BY + ORDER BY aggregate DESC + LIMIT; the top N per group needs window functions (module 10), as does any calculation that must aggregate without collapsing the rows.
• ROLLUP, CUBE and GROUPING SETS add subtotals and totals in a single pass: the ROLLUP of category and year gave 15 rows with the 5 subtotals and the grand total of €727.95.

In the module's last lesson, the HAVING clause, you'll close the circle. You already know how to form groups; now you'll learn to filter them: categories with more than N products, customers with more than one order, products above a certain volume. You'll see why HAVING can use aggregates and WHERE can't —section 2's diagram will tell you on its own—, why WHERE is always preferable when the condition can be evaluated row by row, and you'll finally get the table comparing the three places you can filter in SQL: ON, WHERE and HAVING, closing the thread 03-03 left open.