skip to today's puzzle

GROUP BY gathers matching rows together

Starting table: orders

Starting orders data before the example query
idstatus
101ready
102served
103ready
104cancelled

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

statusCOUNT(*)
ready2
served1
cancelled1

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 course

Ready 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