SQL: AVG with NULL Values

You are correct about the behavior of AVG - use COALESCE to convert the NULLs to 0 in the aggregate.

See this answer in "Why SUM(null) is not 0 in Oracle?"

If you are looking for a rationale for this behaviour, then it is to be found in the ANSI SQL standards which dictate that aggregate operators ignore NULL values.

The relevant code is then, simply:

Avg(Coalesce(col,0))

Use coalesce() to return the real value of zero for null columns:

select avg(coalesce(some_column, 0))
from ...