Why can AVG(salary) differ from SUM(salary) / COUNT(*) on the same table?

## The rule underneath everything Standard SQL gives one rule that explains this whole family of surprises: **with the single exception of `COUNT(*)`, an aggregate function first removes the NULL values from its input, then computes over what remains.** `SUM` adds only the non-NULL values, `MIN`/`MAX` compare only non-NULL values, `AVG` averages only non-NULL values. NULLs are not treated as zero, and they do not poison the result the way they poison a row-level expression — they are simply *not there* as far as the aggregate is concerned. That is different from ordinary arithmetic, where `100 + NULL` is NULL. Inside an aggregate the NULL row is dropped, not propagated. Candidates who expect propagation predict that `SUM(salary)` is NULL as soon as one salary is missing; it is not. ## What AVG actually computes The standard defines `AVG(x)` as `SUM(x) / COUNT(x)`. Both halves are NULL-skipping, so the denominator is the count of **non-NULL** `x` values, not the number of rows. That single fact is the whole question. ```sql -- employees: 10 rows, 6 salaries totalling 600000, 4 NULL salaries SELECT AVG(salary) AS avg_of_present, -- 100000 SUM(salary) * 1.0 / COUNT(*) AS avg_over_rows, -- 60000 COUNT(*) AS rows_total, -- 10 COUNT(salary) AS rows_with_salary -- 6 FROM employees; ``` The numerator is identical in both columns — 600000. Only the divisor moves: 6 versus 10. In general, when the values are non-negative, `SUM(x)/COUNT(*)` is the smaller number, and the gap widens with the share of NULLs. ## Which one do you want? This is a *modelling* question, not a syntax question, and a good answer says so. Ask what a NULL in the column means: - **Unknown / not yet recorded.** A missing salary is not a salary of zero; averaging it in would invent data. `AVG(salary)` over the rows that have a value is the honest number, and you should report the sample size next to it (`COUNT(salary)`) so the reader knows how many rows it rests on. - **Known absence, encoded as NULL.** A commission column where NULL means "earned no commission" really is zero for arithmetic. Then the missing rows belong in the denominator: write `AVG(COALESCE(commission, 0))`, which normalises the input before the aggregate sees it, rather than dividing by `COUNT(*)` yourself. The second form is preferable to the hand-rolled division for a practical reason too: `SUM(x) / COUNT(*)` invites integer division, since both operands may be integers and some engines then truncate. Multiplying by `1.0`, or casting, avoids that — but pushing the fix inside the aggregate avoids the whole issue. ## Edge cases worth knowing - **Every value NULL.** `AVG(x)` returns NULL, not 0 and not an error: there are zero non-NULL inputs, so no division happens at all. `SUM(x)/COUNT(*)` returns NULL as well, because the numerator is NULL. - **No rows at all.** Same result — NULL — and the query still returns exactly one row when there is no `GROUP BY`. - **Adding `WHERE salary IS NOT NULL`.** This does not change `AVG(salary)` by a single digit, because those rows were never in the average. It *does* change everything else in the same `SELECT` list, notably `COUNT(*)`, so it is a blunt instrument in a multi-measure query. - **Silent by design.** The standard defines a completion warning for the case where NULLs were eliminated by a set function, and some engines surface it (SQL Server, for example, reports "Null value is eliminated by an aggregate or other SET operation" when ANSI warnings are on). Most tools never show it, which is exactly why the wrong number ships. ## Saying it in an interview "Aggregates skip NULLs, so `AVG` divides by the count of non-NULL values while `SUM/COUNT(*)` divides by all rows. They differ by exactly the NULL rows. Which one is correct depends on whether a NULL means unknown or means zero; if it means zero, I put `COALESCE` inside the aggregate instead of changing the divisor." That answer shows you know the mechanism *and* that you treat it as a data-meaning decision.