Lessons · SQL · AVG skips the NULLs

Averages skip the blanks

AVG divides the sum by the count of non-NULL values, not by the number of rows. A missing rating is left out entirely, not counted as zero.

Hone is a place to practise programming. This is one of its lessons, written out in full and free to read without an account.

What it is for

Whether a blank means 'no opinion' or 'zero' changes the average, and the database has already decided for you: it means 'not counted'. Reports need to say which one they meant.

How to think about it

Ask what a NULL should mean here. Left out: AVG(col) as it is. Counted as zero: AVG(COALESCE(col, 0)). Report the count beside the average so readers see how many values it rests on.

Worked example

SELECT AVG(stars), COUNT(stars), COUNT(*) FROM reviews;

The average over the non-NULL stars, how many that was, and the row count.

SELECT AVG(COALESCE(stars, 0)) FROM reviews;

The same average treating a missing rating as zero: a different number, on purpose.

Your turn

Average price, treating a missing price as zero.

SELECT AVG((price, 0)) FROM products;

The trap

AVG over a column that is entirely NULL returns NULL, not 0, and COUNT(col) says 0. Show both, or the blank average reads as a bug.

Practise AVG skips the NULLs on HoneA question on it now, a coding challenge where there is one, and it is remembered for review. Free, no email needed.