COUNT, AVG, and the NULLs Your Aggregates Ignore
You SUM the quantities in a warehouse and get 60 across five rows, so you expect the average to be around 12. You run AVG on the same column and it says 20. Nothing is broken. AVG divided by three, not by five, because two of the quantities are NULL and AVG left them out.
This is Part 5 of The UNKNOWN Problem, a series on NULL traps in T-SQL. The earlier parts were about a predicate returning UNKNOWN and dropping a row. This one is about the aggregate functions, which have their own NULL-handling rule: most of them ignore NULL inputs, and they do not all count the same rows.
Here is the setup. Five inventory rows in one warehouse, two of them never counted, so quantity is NULL.
|
1 2 3 4 5 6 |
INSERT INTO dbo.inventory (product_id, warehouse_id, quantity) VALUES (100, 1, 30) , (101, 1, 10) , (102, 1, 20) , (103, 1, NULL) , (104, 1, NULL); |
Now four aggregates over the same column, in one query.
|
1 2 3 4 5 6 7 8 9 |
SELECT COUNT(*) AS row_count , COUNT(quantity) AS quantity_count , SUM(quantity) AS quantity_sum , AVG(quantity) AS quantity_avg FROM dbo.inventory WHERE warehouse_id = 1; /* row_count quantity_count quantity_sum quantity_avg 5 3 60 20 */ |
Five rows, but only three of them have a quantity. SUM is 60. AVG is 20, which is 60 divided by 3, not 60 divided by 5. If you were expecting the average stock across all five rows, you wanted 12, and the query gave you 20.
The cause is NULL again, this time in the NULL-handling rules defined for the aggregate functions rather than in a predicate.[1] Every aggregate in this query except COUNT(*) ignores NULL inputs before it does its work.[2] COUNT(quantity) counts only the rows where quantity is not NULL, so it returns 3 while COUNT(*) returns 5. SUM(quantity) adds the three present values. AVG(quantity) returns int here and works out as SUM(quantity) over the count of non-NULL values, so it divides by 3, not by 5. The calculation excludes the two NULL quantities rather than treating them as zero.
Whether that is right depends on the question you are asking. “Average quantity among the bins we have actually counted” is 20, and AVG(quantity) answers it correctly. “Average quantity across every bin, treating an uncounted bin as zero” is a business rule rather than an arithmetic fact, and under that rule the answer is 12. For that you have to say what a NULL should count as.
|
1 2 3 4 5 6 7 8 |
SELECT AVG(quantity) AS avg_counted_only , AVG(COALESCE(quantity, 0)) AS avg_null_is_zero , SUM(quantity) * 1.0 / COUNT(*) AS avg_over_all_rows FROM dbo.inventory WHERE warehouse_id = 1; /* avg_counted_only avg_null_is_zero avg_over_all_rows 20 12 12.000000000000 */ |
AVG(COALESCE(quantity, 0)) turns each NULL into a zero before the aggregate runs, so all five rows are averaged and the answer is 12. That column is still int, because COALESCE(quantity, 0) is int and AVG of an int returns int. SUM(quantity) * 1.0 / COUNT(*) gets the same 12 a different way, and the * 1.0 promotes the arithmetic to decimal, which is why it displays as 12.000000000000 rather than a plain integer. Neither answer is wrong. Use AVG(quantity) when a NULL should be left out, and supply a replacement value when the business rule gives a NULL a quantity.
COUNT(DISTINCT ...) follows the same rule. It ignores NULL, so a column with three distinct values and some NULLs returns 3, and a column that is entirely NULL returns 0. If a NULL should count as one distinct category, count its presence separately, or fold it into a value with COALESCE first, as long as that replacement value cannot already appear in the column.
A couple of related notes. SUM over a set that is entirely NULL, or over no rows at all, returns NULL rather than 0, so ISNULL(SUM(quantity), 0) is worth having when a report needs a number. And COUNT(*) returns int, which overflows past about 2.1 billion rows with error 8115; COUNT_BIG(*) returns bigint and is the one to reach for on large tables.
The reason this one is easy to trust is that every function returned a number and each answer is internally consistent. With ANSI_WARNINGS ON, which is the default for the common client drivers, SQL Server does raise Null value is eliminated by an aggregate or other SET operation[3], but the result set itself does not identify the excluded rows, and that warning is easy to overlook in a client that does not surface it. The mismatch is only between what AVG averaged and what you assumed it averaged. Two NULLs in the column are enough to move the number.
Next up is Part 6, a single NULL that turns a whole concatenation into NULL, where a + b returns NULL if either side is NULL and a built-up label comes back as NULL instead of the text you wanted.
If a NULL has ever shifted an average or a total on you and the output gave no obvious sign, I would like to hear about it. You can find me on Bluesky and LinkedIn.
References
- NULL Complexities, Part 2 - SQLPerformance, Itzik Ben-Gan. Contrasts row-wise aggregation that ignores NULL with expression evaluation that propagates it, and reproduces the aggregate-elimination warning. ↩
- Aggregate Functions (Transact-SQL) - Microsoft Learn. Documents that aggregate functions other than COUNT(*) ignore NULL inputs. ↩
- SET ANSI_WARNINGS (Transact-SQL) - Microsoft Learn. Governs the warning raised when an aggregate eliminates NULL values. ↩