Averages, totals and HAVING

Once the groups exist you may want to filter them as well: "the genres whose average price is above 80". That condition cannot be asked of a single row, because the average only exists once the group is built. WHERE filters rows; `HAVING` filters groups.

1SELECT genre, AVG(price) AS average
2FROM books
3WHERE year >= 2000
4GROUP BY genre
5HAVING COUNT(*) >= 3
6ORDER BY average DESC;

It runs in this order: WHERE drops the books from before 2000, GROUP BY sorts the rest into genres, HAVING drops the genres with fewer than three books, ORDER BY sorts the result. WHERE runs before the grouping, HAVING after.

Totalling by a period of time is the same pattern: group by the month column, add the amount up with SUM, sort by month. A large share of all reports is those three lines.

Tasks