COUNT, SUM, AVG, MIN, MAX

Why this stage exists

Every SELECT so far has given one output row per input row it kept. The questions people ask most often are not like that: how many orders today, what is the total, the average, the largest? Each reads many rows and answers with one value. These are aggregates, and they are the first operator whose output has nothing to do with the number of rows that went in.

An aggregate is a small machine with three moves. It starts empty, takes a step for each row, and finishes with its answer. COUNT adds one per step; SUM adds the value; MIN keeps the smallest it has seen. The Aggregate operator runs every aggregate of the query side by side over the same rows, and hands up one row of finished answers.

One group, one row

Without GROUP BY, which arrives in the next stage, all the rows the WHERE keeps are one group, and the result is one row. Always: even when no row is kept, select count(*) from t where id > 100 prints one row, 0. That is the difference between "there are none" and "there is no answer".

Because the result is one row, a column on its own has nothing to show: select name, count(*) from t asks for one name to stand for five rows. That is 01507. What can stand beside an aggregate is anything with one value: a literal, another aggregate, or an expression of them, such as sum(a) + count(*).

NULLs, and why SUM of nothing is not 0

Every aggregate except COUNT(*) skips NULLs. COUNT(c) counts the rows where c has a value, and AVG(c) divides by that count, not by the number of rows:

The bottom two rows are the ones to remember. A SUM over no values is NULL, not 0: there was nothing to add, and 0 would claim a total. COUNT is the exception because counting nothing does have an answer.

AVG and its two decimals

The engine has no fractional numbers: every value so far has been an INT, a VARCHAR or a truth value. The mean of 1 and 2 is not an INT, so AVG produces the one fractional value in this part, a number with exactly two decimals. Keep it as a whole number of hundredths, rounded half away from zero, and print it with a point: the mean of 1 and 2 is 1.50, of 1, 1 and 2 is 1.33, of 1, 2 and 2 is 1.67.

An AVG can be compared with an INT and sorted, which the next stage needs for having avg(salary) > 100. Arithmetic on it is 00403: a real DECIMAL type, with its own rules for scale and rounding, belongs to the optional SQL surface track.

Calls, and where they may stand

A call is a name directly followed by (. The name is not a keyword, so a column may still be called count: select count from k reads the column, and select count(count) from k counts it. The names are checked after parsing, in any case: COUNT, count and Count are the same function, and every other name is 01506.

Two places cannot hold an aggregate, and both are 01508. The WHERE decides about one row at a time, before any aggregate has a value. And an aggregate's argument is evaluated once per row, so it cannot itself be an aggregate: sum(count(*)) has no meaning without groups.

Output contract for this stage

These are the exact lines the tests check. The full definition is on the output contract page, section 47.

A call prints as its name in capitals and its argument as section 40 prints expressions, in ast, in plans and in headers. The Aggregate node lists each different call once, in the order the statement first writes it. rows_examined is the scan's: the Aggregate reads every row and adds nothing to the count.

What you should see when done

$droid --db shop.db
Welcome to droid!
>create table orders (id int, qty int, price int);
Create table statement executed successfully
>insert into orders (id, qty, price) values (1, 2, 500);
Insert statement executed successfully (1 rows)
>insert into orders (id, qty, price) values (2, 1, 1200);
Insert statement executed successfully (1 rows)
>insert into orders (id, price) values (3, 300);
Insert statement executed successfully (1 rows)
>select count(*), count(qty), sum(qty * price) as total, avg(price) from orders;
COUNT(*) | COUNT(qty) | total | AVG(price)
3 | 2 | 2200 | 666.67
(1 rows)
[EXEC] rows_examined=3
>select max(price) - min(price) from orders where qty is not null;
MAX(price) - MIN(price)
700
(1 rows)
[EXEC] rows_examined=3
>select sum(qty) from orders where id > 10;
SUM(qty)
NULL
(1 rows)
[EXEC] rows_examined=0
>select id, count(*) from orders;
[ERROR:01507] Column id must appear in an aggregate
>.exit
exiting.. good bye!

Order 3 has no quantity, so it counts for COUNT(*) and not for COUNT(qty), and its product is NULL and left out of the total. The mean price, 2000 / 3, rounds to 666.67. A sum over no rows is NULL. The messages are yours to word; the tests check only the codes.

Common bugs in this stage

• No row for an empty input. The Aggregate must hand up its one row even when its child gave nothing.
• SUM starting at 0 and staying there. An empty or all-NULL SUM must be NULL; track whether it saw a value.
• AVG divided by the row count. Divide by the number of values that were not NULL.
• AVG truncated. 5 / 3 is 1.67, not 1.66; and 1.5 prints as 1.50, not 1.5.
• COUNT(c) counting NULLs. Only COUNT(*) counts every row.
• MIN over text compared as numbers, or case-folded. Compare bytes, as ORDER BY does.
• Calls recognised by keyword. Making count a keyword breaks a column called count; a call is a name followed by (.
• The bare-column check only looking at the top of an item. count(*) + a hides a inside an expression; check every column outside a call.

Language notes

The page above is the same for every language. These are the only language-specific hints.

• C and C++: one struct per aggregate state (kind, count, int64_t total, a value and a "has value" flag) and a switch on the kind for start, step and finish. Print hundredths with "%s%" PRId64 ".%02" PRId64, handling the sign yourself so -0.50 keeps its minus.
• Rust: an enum AggState with one variant per function, and i64::checked_add for SUM. i64::div_euclid is not the rounding you want; compute the remainder and round by hand.
• Zig: a tagged union for the states; @divTrunc and @rem give the quotient and remainder to round from, and std.fmt with {d:0>2} pads the hundredths.