Aggregate functions

Every query so far handed back one row per dish. An aggregate function takes a whole column and brings it down to one value and is used to calculate the minimum, maximum, average, and total value of a column.

name             station  price
Bifteki          grill    18
Baklava          pastry   7
Melitzanosalata  salad    16
Loukoumades      pastry   9
Souvlaki         grill    11
Dakos            salad    14

COUNT(*) counts the rows. The result is a single number, not a list of dishes.

SELECT COUNT(*)
FROM dishes;

count
6

Point it at a column instead, like COUNT(price), and it counts the rows where that column has a value, skipping any that are empty.

MIN and MAX give you the smallest and largest value in a column. They work on any table, not just dishes. Here they are on the staff roster.

name          role       section  years
Giorgos       cook       hot      12
Yannick       cook       cold     5
Sofia         cook       cold     8
Dan           cook       hot      2
Konstantinos  sommelier  bar      15
Eleni         owner      floor    3
Marina        server     floor    1

SELECT MIN(years), MAX(years)
FROM staff;

min  max
1    15

SUM adds a number column up, and AVG gives its average. Both only make sense on numbers.

SELECT SUM(price), AVG(price)
FROM dishes;

sum  avg
75   12.5

A WHERE still runs first, so the function only sees the rows that survived it. Konstantinos keeps his own table, wines, and this averages just the reds on it.

name          type     region     price
Assyrtiko     white    Santorini  14
Agiorgitiko   red      Nemea      12
Xinomavro     red      Naoussa    16
Moschofilero  rosé     Mantinia   10
Malagousia    white    Macedonia  11
Mavrodafni    dessert  Patras     9

SELECT AVG(price)
FROM wines
WHERE type = 'red';

avg
14

One thing to watch: once a SELECT uses an aggregate, every item in it has to be an aggregate too. You cannot ask for name alongside MIN(price), because there is one number on one side and six names on the other.

These give one number for the whole table. Next you will meet GROUP BY, which runs the same functions once per group, so you get one average per station instead of one for the whole menu.