Why can AVG(salary) differ from SUM(salary) / COUNT(*) on the same table?
answer
- two averages, two divisors
- NULL rows never reach the denominator
- COUNT(*) counts rows; AVG counts values
- AVG(x) is SUM(x) / COUNT(x)
basics
~10 sAVG divides by the number of non-NULL salaries, because aggregates discard NULL inputs first. SUM(salary) / COUNT(*) divides by every row, NULL ones included. The two agree only when the column has no NULLs.
solid answer
~40 sEvery aggregate except `COUNT(*)` throws away NULL inputs before it computes anything, so `AVG(salary)` is defined as `SUM(salary) / COUNT(salary)` — sum of the present salaries over the **count of present salaries**. `SUM(salary) / COUNT(*)` keeps the same numerator but divides by the full row count, so each NULL row pulls the result toward zero. With 10 employees, 6 salaries totalling 600000 and 4 NULLs, `AVG` gives 100000 and `SUM/COUNT(*)` gives 60000. Neither is wrong in itself: the question is what a NULL salary *means*. If it means "unknown", `AVG` is right and the NULL rows should not vote. If it means "paid nothing", you want the NULLs in the denominator — write `AVG(COALESCE(salary, 0))` rather than hand-rolling the division.
code
sql · 6 lines-- employees: 10 rows; 6 salaries totalling 600000, 4 salaries NULL
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;go deeper
Memorise the one-liner: aggregates ignore NULLs, and AVG divides by the count of values it actually saw. Be ready to compute both numbers on a small ten-row example at the whiteboard.
Explain the mechanism — NULL elimination happens before the function computes, and AVG is defined as SUM over COUNT of the same column. Show the COALESCE-inside fix and mention integer division if you divide by hand.
Treat it as a data-meaning decision: does NULL mean unknown or zero? Say how you would catch such a discrepancy in a report — reconciling row counts against value counts, and publishing the sample size beside every average.
Own the convention: whether columns are allowed to be nullable at all, whether "missing" and "zero" get distinct encodings, and how metric definitions are documented so two dashboards computing the same average cannot disagree.
## 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.
- What does AVG(rating) return when every row's rating is NULL?NULL. After NULL-skipping there are no inputs left, so there is nothing to sum and nothing to divide by — the engine returns NULL rather than 0 or a division-by-zero error. If the caller needs a number, wrap it: `COALESCE(AVG(rating), 0)`, and be sure that zero is a defensible stand-in for "no data".
- If I want NULL salaries counted as zero, where should COALESCE go?Inside the aggregate: `AVG(COALESCE(salary, 0))`. That replaces the value before NULL-skipping can drop the row, so the row lands in both the sum and the divisor. `COALESCE(AVG(salary), 0)` is a different thing entirely — it leaves the average of the present salaries untouched and only guards the case where there is no data at all.
- Does adding WHERE salary IS NOT NULL change AVG(salary)?No — those rows contribute nothing to `AVG` either way, so the average is identical. It does change any `COUNT(*)`, `SUM` over other columns, or ratio in the same select list, because you have removed whole rows rather than one column's NULLs. Filter for that reason, never to "fix" an average.
Averaging exam scores where some students were absent: AVG grades only the papers handed in, while SUM/COUNT(*) divides by the whole class roster, effectively scoring every absentee zero.
saying these in an interview costs you the question
- Says one NULL makes the whole SUM or AVG NULL
- Believes aggregates treat NULL as zero
- Thinks AVG divides by COUNT(*)
- Adds WHERE col IS NOT NULL expecting AVG to change
- Cannot say which denominator the business actually wants