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?

`NULL`s shouldn't be being used if the actual data should have been `0`s. `NULL`s and `0`s being "equivalent" is a C-ism that has no place in a database. — Damien_The_Unbeliever, Mar 06 '14 at 13:45
I know but i am afraid i have no influence on that in this case — user3364656, Mar 06 '14 at 14:44

score 28 · Accepted Answer · answered Mar 06 '14 at 09:42

28

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

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

answered Mar 06 '14 at 09:42

Bohemian

412,405
93
575
722

1

Lots of variants to this COALESCE syntax. I like ISNULL() functions best (makes more sense linguistically). More info: https://www.w3schools.com/sql/sql_isnull.asp – Dave Skender Dec 03 '19 at 18:23

score 4 · Answer 2 · edited May 23 '17 at 12:25

4

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

edited May 23 '17 at 12:25

Community

1
1

answered Mar 06 '14 at 09:43

user2864740

60,010
15
145
220

score 0 · Answer 3 · answered Feb 28 '17 at 18:23

You can use coalesce or nvl also. Since coalesce examples are already given above, here is an example of nvl

Avg(Nvl(Column_Name),0)

Coalesce, Nvl, Nvl2 functions are handy in such cases of handling null values. You should look up some examples and documentation on these.

score 0 · Answer 4 · answered Oct 07 '18 at 12:20

0

NVL, NVL2, or COALESCE could be used to solve this issue.

like this select avg(4,4,4,4, nvl(null, 0 )) from dual;

answered Oct 07 '18 at 12:20

NatiCog

49
3

score 0 · Answer 5 · answered Jun 01 '21 at 16:30

0

Another possibility is to calculate average using aggregate functions SUM with COUNT

SELECT SUM(column_with_value) / COUNT(primary_column) FROM ...

answered Jun 01 '21 at 16:30

J. Gobet

179
1
5

SQL: AVG with NULL Values

5 Answers5

Linked