One grouping key answers one question. Two keys answer a question about pairs. Three keys answer a question about triples — and 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?" Multi-column groups are how a single flat table answers all three levels at once.
How do multi-column groups answer layered questions?
The rental book is one table with three dimensions in it — city, bike type and status. Each GROUP BY key adds a layer of detail and multiplies the number of 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 status.
What do two grouping keys produce?
Each row is now a city-and-bike-type pair, carrying both a volume count and a revenue total. This is the shape almost every operational report takes, because it lets a reader compare across 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.0
Two things stand out. Cardiff's e-bikes are the single most valuable group in the book — three rentals worth 675 pounds — while Glasgow's cargo bikes are the least valuable at one rental worth 77. And Bristol shows five groups against Cardiff's five and Glasgow's five, even though the city totals differ, because the mix happens to be similar. Volume per group is small, which is exactly why the city-level summary from lesson 17 still earns its place: it answers the question the detail rows cannot.
What does a third key add?
A third key splits each pair by 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 1
Compare Cardiff's e-bikes across the two reports: three rentals in the two-key version, split into two completed and 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 — so the right answer is often two queries, 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 people actually read: 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.0
The WHERE clause runs first, so the cancelled hires are gone before any bucket forms — which is why Cardiff's e-bike count dropped from three to two and its road hire from two to one, while Bristol and Glasgow are untouched. Filtering before grouping is always cheaper, and here it also makes the report more honest: cancelled hires are not revenue.
Can you filter groups on several conditions at once?
HAVING accepts as many conditions as the requirement needs, combined with AND or 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.0
Five groups survive: at least two rentals each and more than two hundred pounds each. Bristol's tandem hire has one rental, Bristol's road hire has two but only 160 pounds, and both fall away for different reasons. Grouped reports are where 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?
GROUP BY alone cannot produce both the detail rows and the per-city totals in one result set — the two live at different levels, and a row belongs to exactly one group. Three approaches exist. PostgreSQL, Oracle and SQL Server offer GROUP BY ROLLUP(city, bike_type), which appends subtotal rows automatically; MySQL and SQLite do not support ROLLUP, so there is no runnable output to show for it here. On SQLite the portable route is a UNION ALL of two grouped queries, and the third route — window functions — arrives in module 11.
Quick reference
- Each additional grouping key multiplies the output rows by the number of distinct combinations.
- Two keys answer pair questions, three keys answer triple questions — use the fewest that answer the question.
- Filtering rows with WHERE before grouping changes which groups exist and how big they are.
- ORDER BY keys mirror the GROUP BY layers: group first, rank inside the group second.
- ROLLUP-style subtotals are a PostgreSQL, Oracle and SQL Server feature; MySQL and SQLite need UNION ALL or window functions.
Layering drill: produce a report of city and bike type with rental count and revenue, then a second report with status added as a third key — and write one sentence naming the business question that only the three-key version can answer.