Five aggregation recipes cover most reporting work: bucket by time, build a leaderboard, split a total with conditional aggregation, adjust for missing data, and express a share of the whole. Each one is a GROUP BY with a particular shape, and once you recognise the shapes you can stop searching for syntax and start composing queries.
Which aggregation recipes solve reporting tasks?
The rental book is one flat table, and every report in this lesson comes from it with no joins at all. That is the point of the recipes: they are patterns, not one-off queries, and each pattern will reappear in every dataset you meet.
Recipe one: bucket revenue by time
Any question about "this month" starts by extracting a period key from a date and grouping on it. The date functions from module 03 do the extraction; GROUP BY does the collapsing:
SELECT strftime('%Y-%m', returned_on) AS month,
COUNT(*) AS rentals,
ROUND(SUM(rental_days * daily_rate), 2) AS revenue
FROM rentals
WHERE returned_on IS NOT NULL
GROUP BY month
ORDER BY month;
month rentals revenue
------- ------- --------
2025-04 10 1506.0
2025-05 9 1700.5
The WHERE clause is doing quiet work: rentals that were cancelled or are still out have no return date, and their NULL would land in a NULL month bucket. Five rows are excluded by that filter, and the monthly totals cover only the nineteen completed hires. A quarter or a week is the same recipe with a different format string, and a rolling window needs a different tool entirely — that is module 11.
Recipe two: rank entities on a measure
A leaderboard is a grouped measure sorted descending and capped. The cap is what makes it a leaderboard rather than a report:
SELECT customer, COUNT(*) AS rentals,
ROUND(SUM(rental_days * daily_rate), 2) AS revenue
FROM rentals
WHERE status = 'completed'
GROUP BY customer
ORDER BY revenue DESC
LIMIT 3;
customer rentals revenue
------------ ------- --------
Yusuf Kaya 2 540.0
Callum Reid 2 360.0
Aisha Rahman 2 293.0
Yusuf Kaya took two e-bikes in Cardiff and paid 540 pounds, and all three leaders rented twice. That last detail is why the count column belongs next to the revenue column: without it, a customer who made one expensive hire looks identical to a loyal one. For a ranked list that shows "top N" per group rather than globally, the technique changes — that is what window functions are for, in module 11.
Recipe three: split one total into several
Conditional aggregation uses a CASE expression inside an aggregate to route rows into different buckets. The trick is that the conditional column is not a separate column in the table — it is computed on the fly:
SELECT city,
ROUND(SUM(rental_days * daily_rate), 2) AS gross,
ROUND(SUM(CASE WHEN status = 'cancelled' THEN 0 ELSE rental_days * daily_rate END), 2) AS net,
SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled
FROM rentals
GROUP BY city
ORDER BY city;
city gross net cancelled
------- ------ ------ ---------
Bristol 1566.5 1566.5 0
Cardiff 1400.0 1233.0 2
Glasgow 1130.0 1130.0 0
Cardiff's gross figure includes 167 pounds of cancelled hires; setting them to zero inside the SUM produces the net figure without a second scan of the table. The cancelled count uses the same shape with a different CASE, which is the general pattern: SUM(CASE WHEN condition THEN 1 ELSE 0 END) counts matching rows, and AVG(CASE WHEN condition THEN 1.0 ELSE 0.0 END) gives their share.
Recipe four: correct for missing values
Two rentals have no discount recorded and five have no return date. Whether that is "no discount" or "unknown discount" changes the answer, so state the assumption explicitly with COALESCE rather than letting the default NULL behaviour decide for you:
SELECT city,
ROUND(SUM(rental_days * daily_rate), 2) AS gross,
ROUND(SUM(rental_days * daily_rate * (1 - COALESCE(discount_pct, 0) / 100.0)), 2) AS net_of_discount,
ROUND(AVG(COALESCE(discount_pct, 0)), 2) AS avg_discount
FROM rentals
GROUP BY city
ORDER BY city;
city gross net_of_discount avg_discount
------- ------ --------------- ------------
Bristol 1566.5 1482.8 4.44
Cardiff 1400.0 1333.55 4.38
Glasgow 1130.0 1011.75 8.57
Glasgow gives away nearly twice as much discount as Cardiff, which a gross-only report would have hidden completely. The same distinction shows up in the average:
SELECT COUNT(discount_pct) AS rows_with_discount,
ROUND(AVG(discount_pct), 2) AS avg_discount_recorded,
ROUND(SUM(COALESCE(discount_pct, 0)) * 1.0 / COUNT(*), 2) AS avg_discount_all_rows
FROM rentals;
rows_with_discount avg_discount_recorded avg_discount_all_rows
------------------ --------------------- -----------------------
22 6.14 5.63
AVG skips the two NULL rows and reports 6.14 percent across the twenty-two rows that recorded one; the second column treats the missing rows as zero discounts and reports 5.63 across all twenty-four. Neither is wrong — they answer different questions — but a report must say which one it is using.
Recipe five: express a share of the whole
A share needs a numerator and a denominator, and the denominator is the total across all groups. A scalar subquery supplies it, and it is the shape that module 06 formalises:
SELECT city,
COUNT(DISTINCT customer) AS customers,
ROUND(100.0 * COUNT(DISTINCT customer) / (SELECT COUNT(DISTINCT customer) FROM rentals), 1) AS pct_of_customers
FROM rentals
GROUP BY city
ORDER BY customers DESC, city;
city customers pct_of_customers
------- --------- ----------------
Bristol 8 44.4
Cardiff 7 38.9
Glasgow 6 33.3
Bristol accounts for 44.4 percent of the eighteen distinct customers, and the three percentages add to 116.6 — because customers appear in more than one city. That is not a bug: a share-of-customers metric counts memberships, not people. When the two must reconcile, count distinct customers across the whole book for the denominator instead.
Quick reference
- Time buckets: extract a period key, filter out NULL dates, group on the key.
- Leaderboards: group by entity, order by the measure, LIMIT to the count you want shown.
- Conditional aggregation: SUM(CASE WHEN ... THEN value ELSE 0 END) splits one total into several.
- Missing values: state the assumption with COALESCE, because AVG and SUM ignore NULLs silently.
- Shares need a whole-table denominator; a scalar subquery supplies it.
Recipe drill: using the rentals table, write three of these recipes — revenue per month, a top-five leaderboard by revenue, and net revenue per city after excluding cancellations — then extend the third one with a column for each city's share of net revenue.