GROUP BY gathers matching rows together
Starting table: orders
| id | status |
|---|---|
| 101 | ready |
| 102 | served |
| 103 | ready |
| 104 | cancelled |
GROUP BY gathers rows that share a value into one group, so you get one row per group. Once the groups exist, COUNT or SUM can work out a total for each one. Every selected column must be the grouped column or a value worked out for the group.
A tiny example
In plain English: For each order status, tell me how many orders have it.
SELECT status, COUNT(*)
FROM orders
GROUP BY status;Query result
| status | COUNT(*) |
|---|---|
| ready | 2 |
| served | 1 |
| cancelled | 1 |
The orders split into three statuses, and each row is one status with its own count.
Common questions
- Why do I get an error when I SELECT a column that isn't in GROUP BY?
- Each group becomes one row, so every selected column must have one value per group. Either add the column to GROUP BY or wrap it in an aggregate such as COUNT, SUM, MIN or MAX.
- Can I filter groups after GROUP BY?
- Yes, with HAVING. WHERE filters rows before they are grouped, and HAVING filters the groups afterwards, for example HAVING COUNT(*) > 1.
Related SQL lessons
Practice GROUP BY step by step
The Foundations Track has beginner puzzles built around this idea. Tap tiles, see real results. No terminal, no setup.
practice this in the courseReady for a break?
A fresh, bite-sized SQL puzzle waits every morning. Tap tiles, see real results. No terminal, no setup, no timers.
Try today's free SQL puzzle