Learn SQL: GROUP BY
Per group with GROUP BY
"One total is nice," says Lien, "but which type of bike brings in the most? And which country do the best customers come from?" That's not one number, but one number per group.
GROUP BY makes groups
GROUP BY type puts all the bikes of the same type on one pile. The aggregate function then works per pile: you get one row per type, with the number of bikes of that type.
SELECT type, COUNT(*) AS number FROM bikes GROUP BY type| type | number |
|---|---|
| cargo bike | 2 |
| city bike | 6 |
| e-bike | 4 |
| kids bike | 2 |
| tandem | 2 |
One rule for your columns
With GROUP BY, every column in your SELECT can only be one of two things: a column you group by, or an aggregate function such as SUM.
Add another column and SQLite won't complain, but it picks an arbitrary value from the group. Other databases give an error. Here's the revenue per bike type.
SELECT bike_type, SUM(amount) AS revenue FROM rentals GROUP BY bike_type| bike_type | revenue |
|---|---|
| cargo bike | 244 |
| city bike | 239 |
| e-bike | 1029 |
| kids bike | 24 |
| tandem | 165 |
Several columns, and sorting
You can group by several columns: then every combination is a group, just like with DISTINCT. Here's the number of bikes per type and brand.
GROUP BY comes after WHERE and before ORDER BY. You can also sort on the result of a function, such as ORDER BY COUNT(*) DESC.
SELECT type, brand, COUNT(*) AS number FROM bikes GROUP BY type, brand ORDER BY type| type | brand | number |
|---|---|---|
| cargo bike | Babboe | 1 |
| cargo bike | Urban Arrow | 1 |
| city bike | Batavus | 2 |
| city bike | Gazelle | 2 |
| city bike | Oxford | 2 |
| e-bike | Gazelle | 1 |
| e-bike | Oxford | 1 |
| e-bike | Stromer | 2 |
Try it yourself
Show the revenue per country, with the country with the highest revenue at the top.
Ready for more?
The full lesson has 6 exercises, from easy to challenging, with hints, points and a streak you can build.
Start the lesson