Fulfledge

Add up and count

INPUT · Slides

Gather and count: putting it together

01 / 03

What you picked up in this chapter

Have a look back at them together.

  • strip the repeats … DISTINCT
  • calculate between columns … + - * /
  • fold rows into one value … SUM / AVG / COUNT / MAX / MIN
  • split into groups … GROUP BY
  • narrow the groups … HAVING

Each one is small. Using them together is the real thing.

02 / 03

The writing order is fixed

A query always comes in the same order. When you are stuck, fit what you want into this shape.

SELECT columns FROM table WHERE row condition GROUP BY what to gather by HAVING group condition ORDER BY sort LIMIT count

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

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

Result

Tsumugi Goods | 21000
Yurari Workshop | 9600

03 / 03

Read a question as six questions

However long the wording, this is all it is asking.

  • which columns do you show? does anything need calculating?
  • which rows do you keep? (WHERE)
  • what do you gather by? (GROUP BY)
  • what aggregation do you want?
  • which groups stay? (HAVING)
  • in what order, and how many?

Jot those down in that order before you write and a long question stops being frightening. Try your hand on the craft market's sales.