How do aggregate functions handle NULL values in SQL?

How to think about it

This is a data-integrity question wearing a SQL costume. The interviewer wants to know whether you instinctively check the NULL rate before trusting an AVG, and whether you know when to substitute NULLs before aggregating versus when to leave them be.

The rule: every aggregate — SUM, AVG, MIN, MAX, COUNT(col) — silently skips NULL rows. Only COUNT(*) counts every row regardless. That single asymmetry is where the skew hides.

A worked example — one column, two very different averages

Five students, three with no score. Watch what each aggregate does with the NULLs:

SELECT COUNT(*)                AS total_students,
       COUNT(score)            AS students_with_score,
       SUM(score)              AS total_score,
       AVG(score)              AS avg_ignoring_nulls,
       AVG(COALESCE(score, 0)) AS avg_treating_nulls_as_zero
FROM exam_results;

total_students	students_with_score	total_score	avg_ignoring_nulls	avg_treating_nulls_as_zero
5	2	170	85.0	34.0

The same 170 points produces 85 or 34 depending entirely on the denominator. AVG(score) divides by 2 (the non-NULL rows); AVG(COALESCE(score, 0)) divides by 5. Which is correct is a domain question: if NULL means “didn’t sit the exam,” 85 is right; if NULL means “scored zero,” 34 is. The SQL can’t decide for you — but it will silently pick 85 unless you intervene.

How to defend against the skew

Always compare COUNT(*) to COUNT(col) before trusting an average:

SELECT COUNT(*)     AS total_rows,
       COUNT(score) AS non_null_rows,
       ROUND(100.0 * COUNT(score) / COUNT(*), 1) AS pct_filled,
       AVG(score)   AS avg_score
FROM exam_results;

total_rows	non_null_rows	pct_filled	avg_score
5	2	40.0	85.0

pct_filled = 40.0 is the alarm: that 85 rests on 40% of the rows. That might be fine (“missing means absent”) or a data-quality red flag (“missing means zero”) — but now you know to ask, rather than quoting 85 into a dashboard blind. Two more behaviours worth naming: a NULL grouping key forms its own group (labelled NULL) under GROUP BY, and MIN/MAX over an all-NULL column return NULL rather than erroring — a common cause of a “successful” query with a baffling NULL output.