Analysis: the final round

01 / 03

The pattern of analysis never changes

Whatever the question, what you do comes apart into four.

what comes out? … orders? units? money?
which tables do you need? … one? a join?
per what do you gather? … the GROUP BY column
how do you narrow and sort? … WHERE / HAVING / ORDER BY

Fill those four in and the query is as good as written.

02 / 03

The writing order is fixed too

Use every piece you have and a query comes out in this order. You cannot move them around, so learn it as a shape.

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

A name you gave with AS can be reused from GROUP BY onwards.

SELECT i.category,       SUM(o.quantity * i.price)         AS sales  FROM orders o  JOIN items i    ON o.item_id = i.id  GROUP BY i.category  HAVING sales >= 5000  ORDER BY sales DESC;

Result

kitchen | 13200
stationery | 9000
cleaning | 5400

03 / 03

When a number comes out, think how to read it

A query that runs is not analysis on its own. It is finished when you can also say what the number means.

is the total big because the price is high, or because plenty sold?
the average is high — an average of how many?
did the fall happen everywhere, or only to some items?

One last try of your hand, on Breeze Mart's three tables.