How does GROUP BY work with more than one column?
SHORT ANSWER
GROUP BY with several columns makes one group for each unique combination of those column values, and aggregates are computed inside each combination.
Explain it simply
Sorting cricket scores by team gives one pile per team. Sorting by team and then by year gives a pile for each team-year pair, such as India-2023 and India-2024. Multi-column GROUP BY creates piles by combinations.
Example
Table sales:
| city | product | amount |
|---|---|---|
| Pune | Tea | 100 |
| Pune | Tea | 150 |
| Pune | Coffee | 200 |
| Delhi | Tea | 80 |
sql
SELECT city, product, SUM(amount) AS total
FROM sales
GROUP BY city, product
ORDER BY city, product;Result:
| city | product | total |
|---|---|---|
| Delhi | Tea | 80 |
| Pune | Coffee | 200 |
| Pune | Tea | 250 |
Order of columns in GROUP BY does not change the groups, only possibly the default output order, which you should control with ORDER BY. Rows with NULL in a grouped column form their own group.
What to say in the interview
"GROUP BY a, b makes one group per distinct (a, b) pair. All non-aggregated selected columns must be listed. Column order does not change the grouping itself."
Common mistakes
- Grouping by too many columns, so every group has one row and the aggregates look useless.