SQL, lesson 4
So far each output row came from one table row. Aggregate functions work the other way: they read many rows and squeeze them into a single value. "How many orders?", "What did we earn?", "What was the hottest day?" are all aggregate questions.
| Function | What it returns |
|---|---|
count(*) | the number of rows |
count(col) | the number of rows where col is not NULL |
sum(col) | the total of the values |
avg(col) | the average, always as a decimal number |
2 exercises to try what you just read.
min(col), max(col) | the smallest and largest value |
You can ask for several at once. Each one becomes a column of the single result row:
WHERE runs before the aggregate, so it chooses which rows get summarised:
Aggregates skip NULL values. That is why count(*) and count(col) can differ: the first
counts rows, the second counts filled-in values. avg also ignores NULL, so a missing rating
does not drag the average down as a zero would.
If there are no rows at all, count gives 0, but sum, avg, min and max give NULL
(printed as an empty line). When you want 0 instead, SQLite offers total(col), which always
returns a decimal and never NULL, or you can wrap a value in coalesce(sum(col), 0).
avg usually produces long decimals. Wrap it in round(value, digits) to keep output readable:
count(DISTINCT col) counts each different value only once, for example the number of
different customers who ordered.
A query like SELECT name, max(price) FROM products is risky: there is one result row, so which
name should it print? Most databases reject it. SQLite allows it and happens to pick the row
with the maximum, but the rule does not hold for other aggregates. In the next lesson you will
learn GROUP BY, the proper way to combine plain columns with aggregates.
The table receipts has one row per sale, with the columns id, amount and paid_by:
Print one row with three values: the number of card sales, their total amount, and the largest card sale. Expected output:
The table readings holds daily temperatures in °C, with the columns day and temp. The
sensor failed on one day, so that temp is NULL:
Print one row with four values: the number of days with a reading, the lowest temperature, the highest temperature, and the average temperature rounded to 1 decimal place. Expected output:
SELECT count(*) FROM orders;SELECT count(*), sum(total), max(total) FROM orders;SELECT sum(total) FROM orders WHERE status = 'paid';SELECT round(avg(rating), 1) FROM reviews;id | amount | paid_by
1 | 12.5 | card
2 | 4.0 | cash
3 | 27.25 | card
4 | 8.75 | card
5 | 15.5 | cash3|48.5|27.25day | temp
Mon | 14.2
Tue | 17.9
Wed | NULL
Thu | 11.4
Fri | 16.04|11.4|17.9|14.9