Fulfledge

Add up and count

INPUT · Slides

Adding it all up

01 / 05

What one row at a time cannot tell you

Last lesson you got the takings for each item. But what the market organiser wants to know is how many went altogether.

Adding up eight rows by eye is fine. Ten thousand records is not. You want a way to fold the whole table into a single value.

02 / 05

Aggregate functions

A function that gathers several rows into one value is called an aggregate function.

  • However many rows go in, one row and one column comes out
  • Inside the brackets goes the column you want gathered

This chapter takes you through sum, average, count, largest and smallest, one at a time. Sum first.

03 / 05

SUM for a total

SUM(column) adds the values in that column across every row. Read it as "from sales, total up all of sold".

Only one row came back. Eight rows folded into a single value.

SELECT SUM(sold) FROM sales;

Result

48

04 / 05

You can total a calculation too

An expression goes inside the brackets just as well. SUM(price * sold) multiplies row by row and then adds the lot — the total takings.

The point is that it happens in two steps: work it out per row, then gather the whole thing.

SELECT SUM(price * sold)  FROM sales;

Result

41400

05 / 05

Narrow first, then total

Put WHERE with it and you get the total of only the rows that match. It goes in the same place it always did.

One warning. Line an aggregate up next to an ordinary column and you get one row back, but the item name is the one from the first row. The rule is: do not mix the two. Let us write some.

SELECT SUM(sold) FROM sales  WHERE stall = 'Tsumugi Goods';

Result

24