GROUP BY collapses rows into summaries by giving the aggregate functions something to collapse within. One grouping key produces one row per key; two keys produce one row per combination. The mechanism is simple, but two rules decide whether the answer is right: every non-aggregated column must appear in the GROUP BY, and the order of the clauses is not a matter of taste.
How does GROUP BY turn many rows into few?
Without GROUP BY, an aggregate reads the entire table and returns exactly one row. Add a grouping key and the engine repeats that whole calculation once per distinct value of that key. The rental book has three cities, so a city grouping returns three rows — one per city, each carrying its own count and total.
SELECT city, COUNT(*) AS rentals, SUM(rental_days) AS days
FROM rentals
GROUP BY city
ORDER BY rentals DESC, city;
city rentals days
------- ------- ----
Bristol 9 44
Cardiff 8 38
Glasgow 7 36
Bristol has nine rentals, Cardiff eight, Glasgow seven — and the totals are close, which is exactly the kind of near-tie that makes a single summary number a poor answer on its own. Note the second ORDER BY key: Bristol and Cardiff are separated by their rental counts, but two cities with the same count would come out in arbitrary order without a tiebreaker.
What does grouping by a key change about the aggregates?
Nothing about their meaning — only their scope. The SUM that previously totalled every rental now totals only the rentals in the current group. Swapping the count for money makes the difference obvious: Bristol is the largest market by revenue as well as by volume, but the gap is smaller than the volume gap suggests.
SELECT city, ROUND(SUM(rental_days * daily_rate), 2) AS revenue
FROM rentals
GROUP BY city
ORDER BY revenue DESC;
city revenue
------- --------
Bristol 1566.5
Cardiff 1400.0
Glasgow 1130.0
Bristol leads on both measures and the two rankings agree — which is itself worth knowing, because a city that led on volume but trailed on revenue would tell a very different story. A report that shows only one grouping is usually hiding a decision from its reader.
What happens when you group by two columns?
The engine forms the combination of the keys and collapses within each combination. Fifteen distinct city-and-bike-type pairs appear across the twenty-four rentals:
SELECT city, bike_type, COUNT(*) AS rentals
FROM rentals
GROUP BY city, bike_type
ORDER BY city, bike_type;
city bike_type rentals
------- --------- -------
Bristol cargo 2
Bristol e-bike 2
Bristol hybrid 2
Bristol road 2
Bristol tandem 1
Cardiff cargo 1
Cardiff e-bike 3
Cardiff hybrid 1
Cardiff road 2
Cardiff tandem 1
Glasgow cargo 1
Glasgow e-bike 1
Glasgow hybrid 3
Glasgow road 1
Glasgow tandem 1
Combinations with no rows simply do not appear — there is no Cardiff-hybrid-and-cargo row, because no rental matched all three at once. Grouping never invents an empty bucket; it only reports the combinations that exist. That single fact is why GROUP BY and a JOIN produce different row counts, and module 05 is built around that difference.
How do you pick the top groups rather than all of them?
Sorting the groups and capping the list is the same two-clause pattern lesson 10 used for rows: ORDER BY the aggregate, then LIMIT. Here the order key is a computed column, which is legal because the aggregate is evaluated before the sort.
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 revenue DESC
LIMIT 3;
city bike_type rentals revenue
------- --------- ------- --------
Cardiff e-bike 3 675.0
Bristol e-bike 2 630.0
Glasgow hybrid 3 384.0
E-bikes dominate the top three, and two of the three are the same bike type in different cities — a pattern that a single-key grouping would have hidden completely. Always add a deterministic tiebreaker to a ranked aggregate report; here the ties are broken by the engine's luck rather than by a rule.
How do NULLs behave as a grouping key?
NULL is a value for grouping purposes: it forms its own group, and every NULL row lands in it. Two rentals have no discount recorded, and they group together under a label of your choosing. Because SQLite lets you reference a SELECT alias inside GROUP BY, the label can live in the same place as the count:
SELECT COALESCE(CAST(discount_pct AS TEXT), 'none') AS discount_band,
COUNT(*) AS rentals,
ROUND(AVG(rental_days), 2) AS avg_days
FROM rentals
GROUP BY discount_band
ORDER BY rentals DESC, discount_band;
discount_band rentals avg_days
------------- ------- --------
0 9 3.89
10 5 6.2
5 4 5.5
15 3 6.67
none 2 2.5
20 1 5.0
The 'none' band holds two rentals with the shortest average hire of any band, which is a small but real signal: the rentals without a recorded discount were also the quickest. Note that the ordering is on the text label, so '10' sorts before '5' — a reminder that grouping by a converted value changes the sort order of the group keys as well.
Quick reference
- GROUP BY divides the rows into buckets and evaluates the aggregates once per bucket.
- Every selected column must be either aggregated or listed in the GROUP BY, or the query is invalid.
- Grouping by two columns produces one row per combination, and combinations with no rows never appear.
- ORDER BY on an aggregate ranks the groups; add a tiebreaker for deterministic output.
- NULL forms its own group, so COALESCE the key if you want a friendlier label.
Grouping drill: write three GROUP BY queries over rentals that answer "how many rentals per city", "how many rentals per city and bike type" and "how many rentals per city, bike type and status" — then explain in one sentence why the third query returns more rows than the second.