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.
Schema
| id | INT |
| owner | TEXT |
| acct_type | TEXT |
| branch | TEXT |
| balance | NUMERIC |
Example data
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.
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.
input rows
| owner | acct_type | balance |
|---|---|---|
| Aisyah | savings | 5200.00 |
| Bala | current | 1300.00 |
| Chong | savings | 8800.00 |
| Devi | current | 450.00 |
| Farid | savings | 2100.00 |
| Gina | current | 6700.00 |
group rows
| acct_type | count | total |
|---|---|---|
| current | 3 | 8450.00 |
| savings | 3 | 16100.00 |
6 input rows collapse into 2 groups — a column not in GROUP BY has no single value left to show per group
Before you run it — there are 6 accounts, 3 savings and 3 current. How many rows will this query return, and what's the average savings balance?
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.
Before you run it — accounts span 2 types and 2 branches. How many rows come back this time, and why more than the single-column version above?
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.
Before you run it — which account type do you think holds more total balance, savings or current? Run it and check.
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.