Fulfledge

Add up and count

INPUT · Slides

Narrow first, then gather

01 / 05

Gathering over only some of the rows

"Keep it to the things at 1000 or under, and show me how many each stall sold." That is narrowing and grouping at the same time.

You can write both. The question is which one bites first.

02 / 05

WHERE comes before GROUP BY

The writing order is FROM → WHERE → GROUP BY → ORDER BY. Everything stays where you learned it, with GROUP BY slotting in behind.

And it bites in that order too. Narrow the rows first, then split what is left into groups.

SELECT stall, SUM(sold) FROM sales  WHERE price <= 1000  GROUP BY stall;

Result

Komorebi Studio | 18
Tsumugi Goods | 20

03 / 05

A whole group can disappear

Did you notice only two stalls came back? Yurari Workshop's things are 1200 and 2400, so all of its rows were gone by the WHERE stage.

A group with no rows left does not appear in the result at all. Not a "0" — no row whatsoever.

04 / 05

Think of it in three steps

Picture the query working in this order and you will not get lost.

  • narrow … WHERE decides which rows stay
  • split … GROUP BY makes the groups
  • gather … the aggregate functions fold each group into one value

Which is why you cannot put an aggregate function in WHERE. At narrowing time, no total has been worked out yet.

SELECT stall, AVG(price) FROM sales  WHERE day = '2026-05-03'  GROUP BY stall;

Result

Komorebi Studio | 700
Tsumugi Goods | 750
Yurari Workshop | 1200

05 / 05

Sorting comes last of all

ORDER BY is last. It arranges the table once the gathering is done, so an aggregate function is perfectly fine in it.

With WHERE, GROUP BY and ORDER BY all together, this is starting to look like a proper query. Let us write some.

SELECT stall, SUM(sold) FROM sales  WHERE price >= 600  GROUP BY stall  ORDER BY SUM(sold) DESC;

Result

Tsumugi Goods | 24
Komorebi Studio | 9
Yurari Workshop | 6