Unit 03.02: Why aggregates skip NULL and reports mislead

Unit ID: SQL-M03-U03 - Estimated active time: 13-16 minutes Objective: state what each aggregate does with NULL and detect a misleading average.

The rule

Aggregates ignore NULLs. COUNT(*) is the exception - it counts rows, not values.

Our tests table has 10 rows, two with no score:

SELECT
  COUNT(*)             AS rows,
  COUNT(score)         AS scored,
  ROUND(AVG(score), 2) AS avg_score
FROM tests;
-- 10 | 8 | 76.25

AVG divided by 8, not 10. That is usually the right statistical choice - you cannot average a value you do not have - but a reader seeing "average score 76.25" over a 10-person cohort will assume it covers all ten.

Where it becomes a real error

SELECT
  COUNT(*)              AS responses,
  COUNT(rating)         AS rated,
  ROUND(AVG(rating), 3) AS avg_rating
FROM feedback;
-- 500 | 440 | 3.045

"Average rating 3.045 from 500 responses" is wrong. It is 3.045 from 440 ratings; 60 people gave no rating at all. Whether those 60 were indifferent, interrupted, or dissatisfied is unknown - and that unknown is exactly what the number hides.

The self-checking pattern

Report the denominator in the same result:

SELECT
  COUNT(*)                                        AS responses,
  COUNT(rating)                                   AS rated,
  ROUND(100.0 * COUNT(rating) / COUNT(*), 1)      AS response_rate_pct,
  ROUND(AVG(rating), 2)                           AS avg_rating
FROM feedback;
-- 500 | 440 | 88.0 | 3.05

Now the average cannot be misread. An 88% response rate is context the reader needs to judge it.

Practice

SUM also skips NULL. Given a table of 100 payments where 10 amounts are NULL, does SUM(amount) return the true total received? What should you check before reporting it?

Takeaway

Every aggregate except COUNT(*) silently narrows its own denominator. Put COUNT(*) and COUNT(column) beside the result so the narrowing is visible.

---

Mark complete