GROUP BY

An aggregate on its own reads the whole table and gives you one number. GROUP BY first sorts the rows into piles that share a value, then runs the aggregate once on each pile.

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

GROUP BY station makes one group per station. COUNT(*) then counts each group on its own, so you get a row per station instead of a single grand total.

SELECT station, COUNT(*)
FROM dishes
GROUP BY station;

station  count
grill    2
pastry   2
salad    2

Notice station is in the SELECT next to the aggregate. That is allowed here because it is the column you grouped by, so there is exactly one value of it per group. Any other plain column would still be rejected, the same as in Lesson 5.

You can ask for several aggregates at once, and they all run per group. It works on any table. Here it is on the staff roster, grouped by role.

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 role, AVG(years)
FROM staff
GROUP BY role;

role       avg
cook       6.75
sommelier  15
owner      3
server     1

WHERE still runs before any of this, so it filters the individual rows on their way in. Here Query Taverna's customer list is filtered down to the loyal ones before anyone gets grouped by tier.

name                 visits  favorite_station  tier
Mrs. Papadaki        42      grill             vip
The Nikolaou family  15      salad             regular
Petros               3       pastry            new
Widow Fotiou         60      grill             vip
Ilias                8       salad             regular
A pair of tourists   1       pastry            new

SELECT tier, COUNT(*)
FROM customers
WHERE visits > 5
GROUP BY tier;

tier     count
vip      2
regular  2

ORDER BY comes after the grouping, so you can sort the group themselves. Here, alphabetically by tier.

SELECT tier, MIN(visits), MAX(visits)
FROM customers
GROUP BY tier
ORDER BY tier;

tier     min  max
new      1    3
regular  8    15
vip      42   60

WHERE filters the dishes before they are grouped. To filter on the group's own number instead, say only the stations averaging over 10, you need HAVING. That is the next lesson, and the difference between the two is the whole point of it.