sushi&syntax
← SQL
rice · fundamentalslesson 03

GROUP BY: counting what the kitchen sold

Aggregates squash rows into answers; GROUP BY decides how many answers you get.

In lesson 02 you picked columns and rows. Tonight the boss asks a different kind of question: how many orders did we get? Which item sold best? Those questions are not about single rows. They are about the whole evening. SQL answers them with aggregates and GROUP BY.

Aggregates squash rows into one answer

COUNT, SUM and AVG take many rows and hand back a single value:

SELECT COUNT(*) FROM orders;
SELECT SUM(qty) FROM orders;
SELECT AVG(qty) FROM orders;
ordersmaki · 2nigiri · 1temaki · 1maki · 1gunkan · 3nigiri · 2COUNT(*)→ 6 rowsSix rows went in. One number came out. That is what aggregates do.ordersmaki · 2nigiri · 1temaki · 1maki · 1gunkan · 3nigiri · 2COUNT(*)→ 6 rowsSix rows went in. One number came out.That is what aggregates do.
fig 1 · an aggregate: many rows in, one value out

That is the defining move. A normal query returns rows. An aggregate query squashes them: COUNT(*) counts them, SUM(qty) adds a column up, AVG(qty) takes the average. One question, one answer.

GROUP BY makes stacks

One total for the whole evening is often too squashed. You do not want “ten pieces sold”, you want “how many of each item”. GROUP BY sorts the rows into stacks first, then the aggregate runs once per stack:

SELECT item, COUNT(*) FROM orders GROUP BY item;
makinigiritemakimakigunkannigirimakimakimaki · 2nigirinigirinigiri · 2temakitemaki · 1gunkangunkan · 1GROUP BY item: one stack per distinct item, one result row per stack.makinigiritemakimakigunkannigirimakimakimaki · 2nigirinigirinigiri · 2temakitemaki · 1gunkangunkan · 1GROUP BY item: one stack per distinct item,one result row per stack.
fig 2 · GROUP BY item: rows sort into stacks, each stack leaves as one row

Read the result like this: every distinct item got a stack, and every stack became exactly one result row. The rows inside a stack are gone from the output; only their summary survives.

Work the stacks yourself

Switch the aggregate and watch the numbers change. Then hover a result row to see which source rows formed its stack:

// try it live · hover a result row
SELECT item, COUNT(*) FROM orders GROUP BY item;
ordersqty
maki2
nigiri1
temaki1
maki1
gunkan3
nigiri2
itemcount
maki2
nigiri2
temaki1
gunkan1

→ 6 rows become 4 groups, one result row each

Notice what never changes: four groups, four result rows. The aggregate only changes what is written on each summary plate, never how many plates there are.

Try this yourself

Rebuild the table from lesson 02 in your playground, then run the stacks:

SELECT item, COUNT(*), SUM(qty) FROM orders GROUP BY item;

Yes, you can ask for two aggregates at once. Then answer with queries: how many pieces went to each table (GROUP BY tbl)? Which question does SELECT item, AVG(qty) FROM orders GROUP BY item; answer, in plain words? Check your numbers against the explorer above.