Chapter 15 · intermediate · 6 min
GROUP BY: one row per group
SUM, AVG, and friends collapse a whole table into one row. GROUP BY changes the question to "one row per category": total balance per account type, average balance per branch. This is the single most-used clause in real analytics SQL.
The dataset
The same `accounts` table from the previous chapter.
One summary per group
GROUP BY acct_type splits the rows into groups, one for savings, one for current, and runs the aggregate once per group:
| acct_type | num_accounts | avg_balance |
|---|---|---|
| current | 3 | 2816.67 |
| savings | 3 | 5366.67 |
The rule to remember: every column in your SELECT must either be in the GROUP BY or wrapped in an aggregate function. acct_type is grouped; balance is aggregated. That's legal.
Per account type
Grouping by more than one column
GROUP BY accepts a comma-separated list; the rows are grouped by the combination of all listed columns. This gives one row per (account type, branch) pair, instead of collapsing branches together:
| acct_type | branch | num_accounts | total_balance |
|---|---|---|---|
| current | KL | 1 | 450.00 |
| current | Penang | 2 | 8000.00 |
| savings | KL | 2 | 7300.00 |
| savings | Penang | 1 | 8800.00 |
One row per account type, per branch
Order the groups
The result of a GROUP BY is just rows, so you can sort them with ORDER BY, including by the aggregate itself:
| acct_type | total_balance |
|---|---|
| savings | 16100.00 |
| current | 8450.00 |
Richest group first
If you've used Excel or Google Sheets
GROUP BY is exactly what a PivotTable does: drag acct_type into Rows, drag balance into Values as "Average", and you've built the first example without writing a formula. Grouping by two columns is the same as dragging a second field into Rows underneath the first. SQL just writes that pivot as one line of text instead of a drag-and-drop layout, which also means it's version-controllable and re-runnable exactly, unlike a manually built pivot.
Ready to practice? GXBank's average balance by type question below is this exact pattern.
GROUP BY shows up in a huge share of real interview questions. Once it's second nature, the next question is almost always "now how do I filter the groups themselves?", which is exactly what HAVING is for.