Sieving the groups

01 / 05

Narrowing by an aggregated number

You can get a total per stall. So how do you write "only the stalls whose total is 18 or more"?

You will want to put it in WHERE, but that is not allowed. At the moment WHERE works, no total has been worked out yet.

SELECT stall, SUM(sold) FROM sales  WHERE SUM(sold) >= 18  GROUP BY stall;

Result

error: misuse of aggregate: SUM()

02 / 05

The thing that narrows the result after gathering is HAVING. It goes after GROUP BY.

Read it as "bundle by stall, and bring me only the groups whose total is 18 or more". Yurari Workshop, on 6, dropped out.

SELECT stall, SUM(sold) FROM sales  GROUP BY stall  HAVING SUM(sold) >= 18;

Result

Komorebi Studio | 18
Tsumugi Goods | 24

03 / 05

They differ in what you can write and in when they bite.

WHERE … before gathering. Looks at one row at a time and decides whether it stays
HAVING … after gathering. Looks at each group's aggregated result and decides whether it stays

"records priced 600 or more" is WHERE; "stalls whose total is 10000 or more" is HAVING. Choose by which one you are looking at to decide.

SELECT stall, COUNT(*) FROM sales  GROUP BY stall  HAVING COUNT(*) >= 3;

Result

Komorebi Studio | 3
Tsumugi Goods | 3

04 / 05

One query can carry a WHERE and a HAVING. The order is fixed like this.

SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT

Below is "stalls whose takings come to 9000 or more, counting only the things priced 600 or more". Two sieves, working at different moments.

SELECT stall, SUM(price * sold)  FROM sales  WHERE price >= 600  GROUP BY stall  HAVING SUM(price * sold) >= 9000;

Result

Tsumugi Goods | 21000
Yurari Workshop | 9600

05 / 05

Sort the groups HAVING left with ORDER BY and you get "a ranking of the stalls that meet the condition".

That is the whole aggregating toolkit. Next comes the round-up for the chapter, where you put all of it together. Let us write some.

SELECT stall, SUM(sold) FROM sales  GROUP BY stall  HAVING SUM(sold) >= 18  ORDER BY SUM(sold) DESC;

Result

Tsumugi Goods | 24
Komorebi Studio | 18