SQL / AGGREGATION WITH GROUP BY
SUM and AVG with NULLs in the mix
Predict and control how SUM and AVG behave when values are missing, why AVG divides by present values, and when SUM returns NULL instead of 0.
What you will learn
- Read AVG(x) as SUM(x) / COUNT(x): the denominator counts values, not rows.
- Expect NULL, not 0, from SUM when every value in the group is NULL.
- Place COALESCE deliberately: outside SUM for reports, inside only if missing means 0.
- Use NULLIF to demote sentinels like -1 so they stop skewing AVG.
Understanding SUM and AVG with NULLs in the mix
SUM and AVG are computed over the values that exist, not over the rows. Before any arithmetic happens the aggregate gathers the column's values for the group and discards every NULL; what remains is what gets added or averaged. That is why SUM over 20, NULL, 22 is 42, even though the scalar expression 20 + NULL + 22 is NULL: scalar arithmetic propagates unknowns, while aggregates drop them first.
The consequence shows up in AVG's denominator. AVG(x) is SUM(x) divided by COUNT(x), never SUM(x) divided by COUNT(*), so it answers a narrower question than most people read into it: the mean among rows that actually had a measurement. For three rows holding 20, NULL, 22 that is 42 / 2 = 21, while the per-row figure is 42 / 3 = 14. Neither number is wrong, but they describe different populations, and the query has to state which one you mean.
When a group has rows but every value in it is NULL, SUM returns NULL rather than 0, because there was nothing to add and SQL will not invent a zero for you; AVG returns NULL for the same reason, since its denominator is zero. Turning that into a displayed 0 is your decision to make: COALESCE(SUM(x), 0) prints zero for the empty case and leaves genuine sums untouched, whereas SUM(COALESCE(x, 0)) rewrites the input rows and, in the case of AVG, changes the meaning by letting rows with no measurement vote as zeros.
WITH readings(sensor, celsius) AS (
VALUES ('s1', 20), ('s1', NULL), ('s1', 22),
('s2', 19), ('s2', NULL),
('s3', NULL), ('s3', NULL)
)
SELECT sensor,
COUNT(*) AS n_rows,
COUNT(celsius) AS n_vals,
SUM(celsius) AS total,
ROUND(AVG(celsius), 2) AS mean
FROM readings
GROUP BY sensor
ORDER BY sensor;SUM and AVG operate only on a column's non-NULL values, so AVG's denominator is COUNT(column) and a group with no values sums to NULL.
Worked examples
Zero-filling moves the average, not the total
Shows that COALESCE(col, 0) inside an aggregate is invisible to SUM but changes AVG.
WITH t(item, price) AS (
VALUES ('a', 10), ('a', NULL), ('a', 20)
)
SELECT SUM(price) AS sum_raw,
SUM(COALESCE(price, 0)) AS sum_zero_filled,
ROUND(AVG(price), 2) AS avg_raw,
ROUND(AVG(COALESCE(price, 0)), 2) AS avg_zero_filled
FROM t;Example explained
Line 1SUM(price) skips the NULL row and adds 10 + 20, so zero-filling cannot change it: adding 0 contributes nothing.
Line 2AVG(price) divides 30 by COUNT(price) = 2 and reports 15.00.
Line 3AVG(COALESCE(price, 0)) divides 30 by 3, because the third row now holds a real value, so it falls to 10.00.
Line 4The COALESCE is therefore a claim about what a missing price means, and only AVG reacts to that claim.
A group where every value is NULL
Demonstrates that SUM over an all-NULL group is NULL and how an outer COALESCE reports it as 0.
WITH t(region, sales) AS (
VALUES ('north', 100), ('north', 250),
('south', NULL), ('south', NULL)
)
SELECT region,
SUM(sales) AS raw_sum,
COALESCE(SUM(sales), 0) AS reported_sum,
ROUND(AVG(sales), 1) AS raw_avg
FROM t
GROUP BY region
ORDER BY region;Example explained
Line 1The south group has two rows, so it still appears in the result, but it has no values to add.
Line 2raw_sum is NULL because SUM received an empty list of values, not because anything went wrong.
Line 3COALESCE(SUM(sales), 0) leaves north's 350 alone and turns south into 0, which is usually what a report wants.
Line 4raw_avg stays NULL for south: an average over no values has no denominator, and printing 0 there would be a false claim.
Sentinel values are not NULLs
Uses NULLIF to remove a -1 placeholder so that AVG stops treating it as data.
WITH answers(id, score) AS (
VALUES (1, 5), (2, -1), (3, 3), (4, -1)
)
SELECT ROUND(AVG(score), 2) AS avg_with_sentinel,
ROUND(AVG(NULLIF(score, -1)), 2) AS avg_real_answers,
COUNT(NULLIF(score, -1)) AS answered
FROM answers;Example explained
Line 1To SQL, -1 is an ordinary number, so AVG(score) averages four values and returns 6 / 4 = 1.50.
Line 2NULLIF(score, -1) yields NULL for the two placeholder rows, and AVG then averages only 5 and 3.
Line 3COUNT(NULLIF(score, -1)) prints 2, the exact denominator AVG used, which is how you audit the figure.
Important notes
Result types differ: SQL Server's AVG over an integer column returns an integer and truncates the fraction, so cast to decimal first; PostgreSQL returns numeric and MySQL a decimal.
Only the outer form survives a completely empty input: with zero rows, SUM(COALESCE(col, 0)) is still NULL because there is nothing to coalesce, while COALESCE(SUM(col), 0) gives 0.
Common mistakes
Treating a NULL in the SUM column as a broken query, or assuming it is 0: a group whose values are all NULL sums to NULL, and passing that into further arithmetic or a NOT NULL column gives NULL or a failed insert.
Hand-rolling the average as SUM(col) / COUNT(*): every missing value silently becomes a zero, so the result comes out lower than AVG(col) — 14.00 instead of 21.00 for sensor s1 above.
Applying COALESCE(col, 0) everywhere as a defensive habit: totals stay correct, but every average is now diluted by rows that never had a measurement.
Try it yourself
Change, predict, then run
In the editor, build a VALUES list for two employees where one employee's three bonus rows are all NULL, then select per employee COUNT(*), COUNT(bonus), SUM(bonus), AVG(bonus) and COALESCE(SUM(bonus), 0). Note which cells come back NULL and which come back 0.
Open the SQL workspaceCheck your understanding
One store has five rows in an amount column holding 100, 200, NULL, NULL, 300. What do SUM(amount) and AVG(amount) return for that store?
- 600 and 200
- 600 and 120
- NULL and NULL
- 600 and NULL
Show answer
Both aggregates discard the two NULLs first, so SUM adds the three remaining values to 600 and AVG divides 600 by COUNT(amount) = 3, giving 200. The tempting 120 is 600 / 5, which is SUM(amount) / COUNT(*): a different metric that treats missing amounts as zeros, and not what AVG computes.