Counting and totals

The lesson

So far each query has returned rows. Often you want a single answer instead: "How many books do we sell?" or "What's our average price?" Aggregate functions squash many rows into one value.

Function	Gives you
`COUNT(*)`	how many rows
`SUM(stock)`	the total of a column
`AVG(price)`	the average
`MIN(price)` and `MAX(price)`	the smallest and largest

SELECT COUNT(*) AS books, AVG(price) AS average_price
FROM books;

Combine with WHERE to summarise only some rows. The filter happens first, then the counting:

SELECT COUNT(*) FROM books WHERE stock = 0;

Do sums inside. SUM(price * stock) is the value of everything on the shelves.

Two handy details:

COUNT(*) counts rows. COUNT(city) counts only rows where city isn't NULL.
COUNT(DISTINCT genre) counts different values, so 6 rather than 20.

Long decimals look messy. ROUND(AVG(price), 2) rounds to two decimal places.

Quick check

1. How many rows does SELECT AVG(price) FROM books; return?

2. Two of eight customers have no city. What does COUNT(city) return?

3. Which comes first when this runs?

SELECT SUM(stock) FROM books WHERE genre = 'Children';