Analysing items: putting it together

01 / 03

The tools you used on the items

Lay out what you have used on the items table so far.

gather … GROUP BY category
count, average, largest and smallest … COUNT / AVG / MAX / MIN
calculate, then gather … SUM(price - cost)
narrow rows … WHERE
narrow groups … HAVING

Each is short on its own. Whether you can layer them is the real thing.

02 / 03

The layering order is fixed

With two kinds of narrowing about, it is easy to put one in the wrong place. Learn it as always this order.

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

You can leave out the parts you do not need, but you cannot move them around.

SELECT category,       COUNT(*) AS products,       AVG(price) AS average_price  FROM items  WHERE stock >= 20  GROUP BY category  HAVING COUNT(*) >= 2  ORDER BY average_price DESC;

Result

kitchen | 4 | 900
stationery | 3 | 750

03 / 03

When your hand stops, take it apart

However long the wording, only four things are being asked. Jot them down and you can build it.

what comes out? … a count? an average? a total?
which rows do you use? … the WHERE condition
per what do you gather? … the GROUP BY column
which groups do you keep? … the HAVING condition

Try your hand on Breeze Mart's items.