Level 7: Grouping with GROUP BY

Lesson

The level 5 aggregates sum up the whole table. Often, you would rather have a summary per category: the total of each seller, the average of each subject, the number of movies of each genre. That is the job of GROUP BY.

GROUP BY region works in three steps. First, SQL sorts the rows into bundles: all the sales whose region is 'North' in one bundle, all those of 'South' in another. Then it computes the aggregates separately in each bundle. Finally, it returns a single row per bundle. The 12 sales thus become 2 rows: North with a total of 2600, South with 2450.

Hence the golden rule: in the SELECT, each column must either appear in the GROUP BY or be inside an aggregate. A result row stands for a whole bundle. The region is the same for the whole bundle: it can be shown. The amount changes from one sale to the next: you can only show its sum, its average or its maximum. A column that breaks the rule has no single value to show.

You can group on several columns: GROUP BY region, month creates one bundle per (region, month) pair. The rows whose grouping column is NULL form a bundle of their own.

GROUP BY does not sort the result: add ORDER BY if the order matters.

The order of execution grows: FROM, WHERE (filters the rows), GROUP BY (forms the bundles), SELECT (computes one row per bundle), ORDER BY. The WHERE therefore acts before grouping: with WHERE month = '2025-01', only the January sales are spread into the bundles.

Syntax

SELECT group_column, COUNT(*), SUM(x)
FROM my_table
WHERE …
GROUP BY group_column
ORDER BY …;

Worked example

SELECT region, SUM(amount) AS total
FROM sales
GROUP BY region;

The 12 sales are split into two groups (North and South); SUM is computed in each one.

Example result

Key points

• One result row per group.
• SELECT columns: in the GROUP BY or inside an aggregate.
• WHERE filters the rows before grouping.

Common pitfalls

• SELECT seller, region, SUM(amount) … GROUP BY seller: region is neither grouped nor aggregated. Most engines reject the query.
• Forgetting GROUP BY gives a single row for the whole table.

SQLite specifics

SQLite accepts a column that is neither grouped nor aggregated: it shows the value of an arbitrary row of the group, without warning. The result looks right but may be wrong: always apply the golden rule.

Going further

GROUP_CONCAT(title, ', ') sticks all the values of a group into a single text, for example the list of titles of each genre (STRING_AGG in PostgreSQL and SQL Server; SQLite also accepts string_agg since version 3.44).

The level’s 5 exercises

1. Guided · Show the total sales (amount) of each seller (seller). (Table: sales)
2. Practice · Show the number of employees in each department. (Table: staff)
3. Practice · Show the average grade of each subject, rounded to one decimal. (Table: grades)
4. Practice · Show the total sales for each region and month pair. (Table: sales)
5. Challenge · Among the books published from 2015 on, show the number of books per genre, from the most to the least represented genre (on ties, by genre in alphabetical order). (Table: books)

In SpeedQL, every query is checked straight away: its result is compared with the expected one, then the query is run again on a hidden control database. Each exercise has written hints, to show only if you get stuck.

See also: the cheat sheet card · the SQL exercises with solutions on this topic