SQL: AVG with NULL Values

sql oracle null average
user3364656 picture user3364656 · Mar 6, 2014 · Viewed 35.8k times · Source

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

Bohemian picture Bohemian · Mar 6, 2014

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

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

Related questions

Update columns with Null values

I tried updating a table as follows: update userloginstats set logouttime = sysdate where logouttime = null; It didn't update the columns with null values. What went wrong?

Read More
Why does Oracle 9i treat an empty string as NULL?

I know that it does consider ' ' as NULL, but that doesn't do much to tell me why this is the case. As I understand the SQL specifications, ' ' is not the same as NULL -- one is …

Read More
Oracle NVL with empty string

I have this table where NULL is the NULL value, not the string NULL: MYCOL -------- NULL example Why does this query not return the NULL row? select * from example_so where nvl(mycol, '') = '';

Read More