Aggregate Functions: COUNT, SUM, AVG, MIN, MAX

So far a query returns a row for every row it matches. An aggregate collapses many rows into one answer: how many users are there, what is the total of all orders, what is the oldest age.

SELECT COUNT(*), SUM(price), AVG(price), MIN(price), MAX(price) FROM orders WHERE status = 'paid'

The result is always one row, with one column per aggregate, even if no row matched.

The five functions

col may also be rowid. The function name can be written in any case; the header always shows it upper case with the argument as written: COUNT(*), SUM(price).

NULL rules

SQL aggregates ignore NULL. That is the one rule behind every special case:

• COUNT(*) counts rows, so NULLs do not matter. COUNT(col) skips the NULLs, so it can be smaller.
• SUM, AVG, MIN and MAX look only at the non-NULL values. If there are none, because the table or the match is empty or the column is all NULL, they return NULL; COUNT returns 0. In particular an empty SUM is NULL, not 0.
• AVG divides by the number of non-NULL values, not by the number of rows: values 25, 30 and NULL average to 27.50, not 18.33.

MIN and MAX follow the engine's usual order, so they work on text too ('Apple' is smaller than 'apple'). SUM and AVG need numbers: asking for them on a text column is an error, SUM needs an INTEGER column: name.

Averages without floating point

AVG is printed with exactly two decimals, rounded half away from zero: 55/2 is 27.50, 1/3 is 0.33, 2/3 is 0.67, -5/2 is -2.50. You can get that with integers only, which avoids the rounding surprises of floats: compute q = round(|total| * 100 / n) as (|total| * 200 + n) / (2 * n) with integer division, then write q / 100, a dot and q % 100 with two digits, with a minus sign in front if the total is negative and q is not 0.

Rules of the select list

A select list is either all plain columns or all aggregates. SELECT name, COUNT(*) FROM t is an error (cannot mix columns and aggregates): which name should the single result row show? (The next lesson, GROUP BY, gives that query a meaning.) ORDER BY has nothing to order with one row, so it is refused with aggregates (ORDER BY is not supported with aggregates). LIMIT and OFFSET apply to the result row: LIMIT 0 or OFFSET 1 leave only the header. SUM(*), COUNT() and the like are syntax errors; only COUNT accepts *.

The rows are fetched through the same access path as any query (an index lookup when the WHERE allows it), every one of them is read, and .examined reports how many.

What you build

The starter parses the query, picks the rows and prints the single result line. You write the arithmetic:

• aggregate: one function over the list of values of the matching rows.
• format_avg: the two-decimal average.

Common mistakes

• Counting NULLs in COUNT(col) or dividing by the wrong number in AVG.
• Returning 0 for an empty SUM, or crashing when there is nothing to divide.
• Using floating point for AVG, so that a half rounds the wrong way.
• Comparing text with a numeric order in MIN and MAX.

Your exercise

You implement aggregate and format_avg. The mistake to avoid: treating NULL as 0.