SQL: aggregates ignore NULL, so AVG is not SUM / COUNT(*)

AVG divides by the number of non-NULL values. If some rows have no value, the average is over fewer rows than the table has - which is usually right, and occasionally a surprise.

Code

-- scores: 10, 20, NULL, NULL
SELECT SUM(score)   AS total,     -- 30
       COUNT(*)     AS rows,      -- 4
       COUNT(score) AS scored,    -- 2
       AVG(score)   AS avg;       -- 15, not 7.5

-- treat missing as zero if that is the intent
SELECT AVG(COALESCE(score, 0)) FROM results;   -- 7.5

Output

total | rows | scored | avg
   30 |    4 |      2 |  15

AVG(COALESCE(score,0)) -> 7.5

-- Both are defensible; they answer different questions. Decide which one
-- the report means before someone else assumes the other.

Run this yourself in the Online Java Compiler, spin up a live REST API in the API Sandbox, or practise with Java interview questions.

Published 2026-08-25