What do the five basic aggregate functions do, and what do they return when there are no rows?
There are five of them, and they all take a whole group of rows and give back one value.
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL,
amount DECIMAL(10, 2) NOT NULL
);
INSERT INTO orders (id, customer_id, amount) VALUES
(1, 1, 250.00),
(2, 1, 120.00),
(3, 2, 900.00);
Here is what each one does. The function COUNT counts the rows. The function SUM adds the values up. The function AVG divides the total by the number of values. The function MIN gives the smallest value. And the function MAX gives the largest one.
So what do they give back when there are no rows at all? This is worth knowing because the answers are not all the same.
The function COUNT gives back zero, because there are no rows to count. The function SUM gives back a missing value, because there is nothing to add up and the sum of nothing is not zero. The function AVG gives back a missing value, because you cannot divide by nothing. And the functions MIN and MAX give back a missing value for the same reason.
So the rule to remember is this. COUNT is the only one of the five that gives back a number when there is nothing to work out. If you add up the sums of a set of groups and one of the groups was empty, then the total has a missing value in it, and you usually have to wrap it in the function COALESCE so that the report still works.
There is one more thing about them that matters. They all ignore the missing values in the column. So if one of the amounts is missing, the function SUM adds up the ones that are there and does not count the missing one at all.
The next two questions in this topic put them to work and look at a trap.