AVG and NULL rows

Question

AVG and NULL rows

Accepted Answer

AVG ignores NULLs in both the numerator and denominator. It computes (80 + 90) / 2 = 85, not (80 + 90) / 4 = 42.5. This means AVG and SUM/COUNT(*) can give inconsistent pictures of the data when NULLs are present. If you want NULLs to count as 0, use AVG(COALESCE(score, 0)) or SUM(score) / COUNT(*).