Five rows in, three rows out — one per distinct category. A grouped query returns as many rows as there are groups, and a group exists only if at least one row has that value. A category with no orders does not appear at all; listing every category, including the empty ones, needs a join against a categories table (Module 7).

The rule for the SELECT list

Once rows are grouped, each output row stands for a whole group. So everything in the SELECT list must have one value per group:

• a column listed in GROUP BY — it is the same for every row in the group;
• an aggregate — it computes one value from the group;
• an expression built from those two.

SELECT category, COUNT(*), MAX(amount), ROUND(AVG(amount), 2)   -- fine
FROM orders GROUP BY category;

SELECT category, customer_id, COUNT(*)                            -- which customer_id?
FROM orders GROUP BY category;

The second query asks for one customer_id per category when a category has many. Standard SQL rejects it; MySQL 8 rejects it too by default, with error 1055 — the ONLY_FULL_GROUP_BY rule, covered in detail in the grouping-pitfalls lesson.

Grouping by several columns

List several columns and a group is each distinct combination:

SELECT region, category, SUM(amount) AS revenue
FROM orders
GROUP BY region, category
ORDER BY region, category;

Only combinations that occur are returned — there is no north, toys row because no such order exists. The order of columns inside GROUP BY does not change the groups; ORDER BY decides how they are printed.

Grouping by an expression

You can group by any expression, which is how dates are rolled up into periods:

SELECT DATE_FORMAT(order_date, '%Y-%m') AS month,
       COUNT(*)                         AS orders,
       SUM(amount)                      AS revenue
FROM orders
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
ORDER BY month;

MySQL also lets you name the alias in GROUP BY — GROUP BY month — which saves repeating the expression. PostgreSQL allows that too; SQL Server does not, so portable code repeats the expression. Other useful groupings:

GROUP BY YEAR(order_date), MONTH(order_date)   -- year and month as numbers
GROUP BY DAYNAME(order_date)                    -- sales by weekday, every week together
GROUP BY FLOOR(age / 10) * 10                   -- age bands: 20, 30, 40…
GROUP BY amount >= 100                          -- two groups: 0 (small) and 1 (large)
GROUP BY LOWER(TRIM(email))                     -- addresses that differ only in case/spaces

Grouping by MONTH(order_date) alone puts March 2023 and March 2024 in the same group — sometimes intended (seasonality), usually a bug.

WHERE before GROUP BY

WHERE filters rows before they are grouped, so it changes what each group contains:

SELECT category, COUNT(*) AS big_orders
FROM orders
WHERE amount >= 50
GROUP BY category
ORDER BY category;

Categories with no order of at least 50 disappear entirely, because none of their rows reaches the grouping. That is often what you want; when it is not — "every category, with its count of big orders, zero included" — use conditional aggregation (two lessons on) instead of WHERE.

The logical order of a grouped query is worth memorising, since many interview questions test it:

1. FROM — the rows
2. WHERE — keep some rows
3. GROUP BY — form groups
4. HAVING — keep some groups (next lesson)
5. SELECT — compute the output columns
6. ORDER BY — sort
7. LIMIT — cut

Sorting groups

MySQL 5.7 and earlier sorted the result of GROUP BY by the grouping columns as a side effect; MySQL 8 does not, and no other database promises it. Always write ORDER BY. You can sort by an aggregate, which is how "top" questions are answered:

SELECT customer_id, SUM(amount) AS spent
FROM orders
GROUP BY customer_id
ORDER BY spent DESC, customer_id
LIMIT 3;

The second sort key breaks ties, so the top three is the same list every time.

NULL forms one group

GROUP BY puts all NULLs together in one group, even though NULL = NULL is not true in a WHERE. Orders with no region come out as one row with region NULL:

SELECT COALESCE(region, 'unknown') AS region, COUNT(*) AS orders
FROM orders
GROUP BY COALESCE(region, 'unknown')
ORDER BY region;

Labelling the NULL group makes a report readable; leaving it out silently is a common way for totals not to add up.

GROUP BY versus DISTINCT

With no aggregates, GROUP BY returns the distinct combinations, exactly like SELECT DISTINCT:

SELECT DISTINCT region FROM orders;
SELECT region FROM orders GROUP BY region;      -- same rows

Use DISTINCT to say "unique values" and GROUP BY when you compute something per group. Readers expect it that way round.

Pitfalls

• A plain column in SELECT that is not in GROUP BY.
• Expecting groups with no rows to appear.
• Grouping by MONTH(d) when the report spans years.
• Relying on GROUP BY to sort.
• Filtering with WHERE when every group should still appear.
• Forgetting that NULLs form their own group.

Key takeaways

• GROUP BY returns one row per distinct value (or combination) of its columns.
• Select only grouping columns, aggregates, and expressions of them.
• Group by expressions like DATE_FORMAT(d, '%Y-%m') to roll dates into periods.
• WHERE runs before grouping; ORDER BY after — always write it.
• All NULLs fall into one group.