One grouping key answers one question. Two keys answer a question about the pairs. Three keys answer a question about the triples. The real skill is recognising that a business question is usually layered. How does each city do, and within each city how does each bike type do, and within each of those how do the statuses split? The multi-column groups are how a single flat table answers all three of the levels at once.
The previous lesson in this module covered "When Does HAVING Filter What WHERE Cannot?", and the next lesson in this module is about "Which Aggregation Recipes Solve Reporting Tasks?".
How do the multi-column groups answer the layered questions?
The rental book is one table with three dimensions in it. There is the city, there is the bike type, and there is the status. Each GROUP BY key adds a layer of detail and it multiplies the number of the output rows, because every distinct combination of the keys becomes its own bucket. One key gave three rows in lesson 17. Two keys give fifteen. Three keys give eighteen, because a city-and-type pair can itself split by the status.
What do the two grouping keys produce?
Each row is now a city-and-bike-type pair, and it carries both a volume count and a revenue total. This is the shape that almost every operational report takes, because it lets a reader compare across the one dimension while holding the other fixed.
SELECT city, bike_type, COUNT(*) AS rentals,
ROUND(SUM(rental_days * daily_rate), 2) AS revenue
FROM rentals
GROUP BY city, bike_type
ORDER BY city, bike_type;city bike_type rentals revenue
------- --------- ------- --------
Bristol cargo 2 269.5
Bristol e-bike 2 630.0
Bristol hybrid 2 312.0
Bristol road 2 160.0
Bristol tandem 1 195.0
Cardiff cargo 1 231.0
Cardiff e-bike 3 675.0
Cardiff hybrid 1 192.0
Cardiff road 2 224.0
Cardiff tandem 1 78.0
Glasgow cargo 1 77.0
Glasgow e-bike 1 225.0
Glasgow hybrid 3 384.0
Glasgow road 1 288.0
Glasgow tandem 1 156.0There are two things that stand out. The e-bikes of Cardiff are the single most valuable group in the book, with three rentals worth the 675 pounds, while the cargo bikes of Glasgow are the least valuable at the one rental worth the 77. And Bristol shows five groups against the five of Cardiff and the five of Glasgow, even though the city totals differ, because the mix happens to be similar.
The volume per group is small, which is exactly why the city-level summary from lesson 17 still earns its place. It answers the question that the detail rows cannot.
What does a third key add?
A third key splits each pair by the status, which is how a cancelled hire gets separated from a completed one without any arithmetic.
SELECT city, bike_type, status, COUNT(*) AS rentals
FROM rentals
GROUP BY city, bike_type, status
ORDER BY city, bike_type, status;city bike_type status rentals
------- --------- ------- -------
Bristol cargo completed 2
Bristol e-bike active 1
Bristol e-bike completed 1
Bristol hybrid completed 2
Bristol road completed 2
Bristol tandem active 1
Cardiff cargo completed 1
Cardiff e-bike cancelled 1
Cardiff e-bike completed 2
Cardiff hybrid completed 1
Cardiff road cancelled 1
Cardiff road completed 1
Cardiff tandem active 1
Glasgow cargo completed 1
Glasgow e-bike completed 1
Glasgow hybrid completed 3
Glasgow road completed 1
Glasgow tandem completed 1Compare the e-bikes of Cardiff across the two reports. There are three rentals in the two-key version, and they are split into the two completed and the one cancelled in the three-key version. The detail is what turns "Cardiff does well on e-bikes" into "Cardiff does well on e-bikes once you set the cancelled hire aside".
Each additional key costs rows, and the reader who wants the summary has to scroll past the detail. The right answer is often two queries and not one enormous GROUP BY.
How do you order a layered report?
Ordering by the first key and then by a measure inside it produces the report shape that people actually read. You get the city block, and inside it the biggest earners first. The ORDER BY has exactly the same layered semantics as the GROUP BY.
SELECT city, bike_type, COUNT(*) AS rentals,
ROUND(SUM(rental_days * daily_rate), 2) AS revenue
FROM rentals
WHERE status <> 'cancelled'
GROUP BY city, bike_type
ORDER BY city, revenue DESC;city bike_type rentals revenue
------- --------- ------- --------
Bristol e-bike 2 630.0
Bristol hybrid 2 312.0
Bristol cargo 2 269.5
Bristol tandem 1 195.0
Bristol road 2 160.0
Cardiff e-bike 2 540.0
Cardiff cargo 1 231.0
Cardiff road 1 192.0
Cardiff hybrid 1 192.0
Cardiff tandem 1 78.0
Glasgow hybrid 3 384.0
Glasgow road 1 288.0
Glasgow e-bike 1 225.0
Glasgow tandem 1 156.0
Glasgow cargo 1 77.0The WHERE clause runs first, so the cancelled hires are gone before any bucket forms. That is why the e-bike count of Cardiff dropped from three to two and its road hire from two to one, while Bristol and Glasgow are untouched.
Filtering before the grouping is always cheaper, and here it also makes the report more honest. The cancelled hires are not the revenue.
Can you filter the groups on several conditions at once?
Yes. The HAVING accepts as many conditions as the requirement needs, and it combines them with the AND or the OR like any other predicate.
SELECT city, bike_type, COUNT(*) AS rentals,
ROUND(SUM(rental_days * daily_rate), 2) AS revenue
FROM rentals
WHERE status <> 'cancelled'
GROUP BY city, bike_type
HAVING COUNT(*) >= 2 AND SUM(rental_days * daily_rate) > 200
ORDER BY city, revenue DESC;city bike_type rentals revenue
------- --------- ------- --------
Bristol e-bike 2 630.0
Bristol hybrid 2 312.0
Bristol cargo 2 269.5
Cardiff e-bike 2 540.0
Glasgow hybrid 3 384.0Five of the groups survive. There are at least two rentals each and more than the two hundred pounds each. The tandem hire of Bristol has the one rental, the road hire of Bristol has the two but only the 160 pounds, and both of them fall away for the different reasons.
The grouped reports are where the compound thresholds earn their keep, because a single condition almost never captures a real business rule.
How do you get a subtotal row for each city?
The GROUP BY alone cannot produce both the detail rows and the per-city totals in one result set. The two of them live at the different levels, and a row belongs to exactly one group.
There are three approaches. PostgreSQL, Oracle and the SQL Server offer the GROUP BY ROLLUP(city, bike_type), which appends the subtotal rows automatically. MySQL and SQLite do not support the ROLLUP, so there is no runnable output to show for it here. On SQLite the portable route is a UNION ALL of the two grouped queries, and the third route, which is the window functions, arrives in module 11.
Quick reference
- Each additional grouping key multiplies the output rows by the number of the distinct combinations.
- Two keys answer the pair questions and three keys answer the triple questions, so you use the fewest that answer the question.
- Filtering the rows with the WHERE before the grouping changes which groups exist and how big they are.
- The ORDER BY keys mirror the GROUP BY layers. You group first, and you rank inside the group second.
- The ROLLUP-style subtotals are a PostgreSQL, an Oracle and a SQL Server feature, while MySQL and SQLite need the UNION ALL or the window functions.
Layering drill: you produce a report of the city and the bike type with the rental count and the revenue, and then a second report with the status added as a third key. You write one sentence that names the business question that only the three-key version can answer.