Learn/SQL/4. Aggregates
Lesson 4 of 8
Sign in

SQL, lesson 4

Aggregates

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.

The five you will use most

FunctionWhat 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.

Exercise 1

Till receipts

Press Ctrl Enter or Check to test your code.

Exercise 2

Weather station

Press Ctrl Enter or Check to test your code.

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:

How NULL is treated

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).

Tidy numbers

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.

Mixing aggregates and plain columns

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.

Further reading

  • SQLite: aggregate functions
  • SQLite: round() and coalesce()

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   | cash
3|48.5|27.25
day | temp
Mon | 14.2
Tue | 17.9
Wed | NULL
Thu | 11.4
Fri | 16.0
4|11.4|17.9|14.9