GROUP BY: making piles and asking about each one

Piles

GROUP BY city takes all your rows and sorts them into piles, one pile per distinct city. Then every aggregate you write (COUNT, SUM, AVG, MAX) is computed inside one pile, and you get one output row per pile.

SELECT   city, COUNT(*) AS orders, SUM(amount) AS revenue
FROM     orders
WHERE    placed_at >= '2026-03-01' AND placed_at < '2026-04-01'
GROUP BY city
ORDER BY revenue DESC;

Thirty thousand rows go in. Fourteen rows come out, one per city. That is the whole idea.

Why the database refuses your query

Add a column and it breaks:

SELECT city, customer_name, COUNT(*)
FROM orders
GROUP BY city;
-- ERROR: column "orders.customer_name" must appear in the GROUP BY clause
--        or be used in an aggregate function

The error looks bureaucratic. It is not. The Lagos pile contains 900 rows with 900 different customer names, and the output has exactly one slot for Lagos. Which of the 900 should go there? There is no right answer, so the database refuses to invent one.

Every column in your SELECT must be either something you grouped by (so it is the same for the whole pile) or wrapped in an aggregate (so the pile is squeezed into a single value). MySQL in some configurations will let this slide and pick a name at random. That is worse, not better.

The three counts

These are different questions and they routinely get confused.

COUNT(*) counts rows in the pile. Full stop.

COUNT(phone) counts rows in the pile where phone is not NULL. Aggregates skip NULLs, always.

COUNT(DISTINCT customer_id) counts how many different customers appear in the pile.

So in a table where one row is one order:

• COUNT(*) is how many orders
• COUNT(DISTINCT customer_id) is how many customers ordered

Neither is more correct. They answer different questions, and the whole skill is knowing which question you were asked. A repeat customer with four orders is one customer and four orders, and both facts are true at the same time.

WHERE before, HAVING after

WHERE filters rows on their way into the piles. HAVING filters whole piles on their way out.

SELECT   city, SUM(amount) AS revenue
FROM     orders
WHERE    status = 'paid'          -- discard rows before piling
GROUP BY city
HAVING   SUM(amount) > 500000     -- discard piles after summing
ORDER BY revenue DESC;

You cannot put SUM(amount) > 500000 in WHERE, because when WHERE runs the piles do not exist yet. You can put status = 'paid' in HAVING in some databases, but do not: it means the same thing, runs later, and reads as though it is a statement about the city rather than about individual orders.

Two things worth knowing early

No GROUP BY means one pile containing everything. SELECT COUNT(*) FROM orders is a group query with a single group. That is why you get one row back.

Grouping by two columns makes a pile per combination. GROUP BY city, month gives one row per city per month. If you have 14 cities and 12 months, expect up to 168 rows, and expect fewer, because combinations with no rows produce no pile at all. A city with zero orders in July does not appear as a zero; it is not there. If your report needs that zero, you have to supply the list of months yourself and join to it. Missing rows are much harder to notice than wrong numbers.