SQL: AVG with NULL Values

Question

As far as I understood, the AVG() function ignores NULL Values.

So AVG(4,4,4,4,4,NULL) --> 4

In my case I don't want this to happen.

I need a solution like that: AVG(4,4,4,4,4,NULL) --> 3,33

without replacing the NULL values directly in the table itself.

Is there any way to do this?

Answer 1

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

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

Answer 2

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))