SQL, lesson 5
Aggregates give you one number for the whole table. Often you want one number per something:
sales per region, goals per player, orders per customer. GROUP BY does that. It sorts rows into
buckets that share the same value, then runs your aggregates once per bucket.
If sales has rows for three regions, this prints three rows: each region next to its total.
In a grouped query, every column in SELECT should be either:
GROUP BY (it has one value per group), orAnything else has no single value per group. SQLite will still run the query and pick a value from some row in the group, which is a quiet source of wrong answers. Follow the rule and you never have to think about it.
You can group by more than one column. GROUP BY region, product makes one group for every
region and product pair that appears in the data.
2 exercises to try what you just read.
GROUP BY does not promise any order. Add ORDER BY after it. You may sort by an aggregate,
and naming it with AS keeps that tidy:
WHERE filters rows before they are grouped. It cannot see aggregates, because they do not
exist yet. To filter after grouping, using an aggregate, use HAVING:
A query can use both. A handy way to remember the order SQLite works in: FROM picks the table,
WHERE drops rows, GROUP BY makes buckets, HAVING drops buckets, SELECT builds the output,
ORDER BY sorts it, and LIMIT cuts it short.
The table sales has the columns region, product and amount:
Print each region with the number of sales and the total amount, ordered by the total from
highest to lowest. Expected output:
The table loans records library loans, with the columns reader and book:
Print the readers who borrowed at least two books, with how many they borrowed, in alphabetical order of reader. Expected output:
SELECT region, sum(amount)
FROM sales
GROUP BY region;SELECT region, sum(amount) AS total
FROM sales
GROUP BY region
ORDER BY total DESC;SELECT customer, count(*) AS orders
FROM orders
WHERE status = 'paid' -- only paid orders are counted
GROUP BY customer
HAVING count(*) >= 3 -- only customers with 3 or more of them
ORDER BY customer;region | product | amount
North | Kettle | 40
South | Toaster | 25
North | Toaster | 25
East | Kettle | 40
South | Blender | 60
North | Blender | 60
East | Toaster | 25North|3|125
South|2|85
East|2|65reader | book
Ines | Dune
Tom | Emma
Ines | Matilda
Kofi | Dune
Tom | Ivanhoe
Ines | Beloved
Ruth | Emma
Kofi | PersuasionInes|3
Kofi|2
Tom|2