Skip to content
Quantoros

What does AVG do with NULLs?

SQLLevel: FoundationAggregations Basics

What does AVG do with NULLs?

Hints
  • Define one output row and the source-table keys.
  • Check duplicates, NULLs and the order of filtering and aggregation.

Answer

AVG ignores NULL values. It calculates the average of non-NULL values only.

-- If scores are: 80, 90, NULL, NULL
-- AVG returns 85, not 42.5 (which it would be if NULLs were treated as 0)
SELECT AVG(score) FROM test_results;

If you want NULLs treated as zero, use AVG(COALESCE(score, 0)).

Source and licence: SQL Interview Questions — Arda Kocadoru · MIT

Related questions