SQL: Why COALESCE(x, 0) Drags an Average Down
Replacing NULL with 0 before avg turns every missing value into a real zero, so the average falls. Let avg skip NULL, and count the missing rows in their own column.
The symptom
You report how long each rental is kept, by genre. Romance averages 1 day, Comedy less than a day, and the store overall 2.19 days. The returned Romance rental was kept for 3 days, and no Comedy rental came back on the day it went out.
Why it happens
6 of the 26 rentals haven't come back, so their returned_date
is NULL, and so is returned_date - rental_date.
avg already skips NULL. Wrapping the length in
coalesce(..., 0) turns each missing length into a rental kept
for zero days, and those zeros count. Romance has one returned rental of 3
days and two still out, so the average becomes (3 + 0 + 0) / 3 = 1.
The coalesce usually goes in to tidy the output: a blank cell
looks broken, and 0 looks like an answer. It is the wrong answer when
NULL means "not known yet" rather than "none".
The fix
Leave the NULL in, so avg skips it. Both queries
count the returned rentals in their own column, so the reader knows what
each average covers; only the average changes:
select m.genre,
count(*) as rentals,
count(r.returned_date) as returned,
avg(coalesce(r.returned_date - r.rental_date, 0)) as avg_days
from rentals as r
join movies as m on m.movie_id = r.movie_id
group by m.genre
order by m.genreselect m.genre,
count(*) as rentals,
count(r.returned_date) as returned,
avg(r.returned_date - r.rental_date) as avg_days
from rentals as r
join movies as m on m.movie_id = r.movie_id
group by m.genre
order by m.genre
Romance and Drama both average 3 days, and Comedy 1. The
returned column shows how few rentals some of those averages
rest on: Romance has one. Over the whole store the average is 2.85 days, not
2.19.
select count(*) as rentals,
count(returned_date) as returned,
avg(returned_date - rental_date) as avg_days
from rentals; coalesce(x, 0) is correct when NULL really means
zero, such as a discount column left empty for orders with no discount. Ask
what the missing value means before you replace it. For a total,
sum gives the same answer either way, because adding 0 changes
nothing; it is avg, and anything divided by a count, that
moves.
The 6 open rentals have each been out for a while already, so 0 days is the one value they certainly don't have. To estimate their length so far, measure up to a fixed date, such as the end of the data, rather than pretending they ended at once. Lesson 12.5 · Late and overdue rentals works with the open ones.