How do the aggregate functions treat NULL values, and why does that matter in practice?
Every aggregate function ignores the missing values, and the one exception is the count with a star.
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL,
amount DECIMAL(10, 2)
);
INSERT INTO orders (id, customer_id, amount) VALUES
(1, 1, 250.00),
(2, 1, NULL),
(3, 1, 120.00);
-- Three rows, but only two amounts.
SELECT
COUNT(*) AS row_count,
COUNT(amount) AS value_count,
SUM(amount) AS total,
AVG(amount) AS average
FROM orders;
row_count value_count total average
3 2 370.00 185.00
So what is happening? The star counts all three rows. The column name counts the two rows where the amount is there, because the row with the missing amount is skipped. The sum adds up the two amounts that are there and ignores the missing one. And the average divides the total by the count of the values, so it is 185 and not 123.
So why does that matter in practice? Three reasons.
The first one is that the number you get is not the number you expect. A report that says "the average order was 123" when the real average was 185 is wrong, and nothing in the query told you that it was wrong.
The second one is that a missing value can change the shape of the answer. If every amount in a group is missing, then the sum is a missing value and not a zero, and the group looks different from a group that genuinely added up to zero.
The third one is that the missing value can disappear without anybody noticing. The function COUNT(amount) quietly leaves out the row, so the count of the rows and the count of the values are different, and a report that uses one of them where the other was meant says something that is not true.
There is one more thing to know. The function COUNT(*) and the function COUNT(column) count different things, and the difference only shows up when the column can be missing a value.
The next question in this topic looks at the empty case.