• Questions
• Average of 0 and Value - gives incorrect output. Is there a way to ignore the 0 during the average function.

Thanks for the question, D.

Asked: February 13, 2020 - 8:40 pm UTC

Answered by: Chris Saxon - Last updated: February 17, 2020 - 1:46 am UTC

Category: SQL - Version: 12.2

Viewed 100+ times

Hi Tom,

I am having a SQL output as follows.

```A                B                     C             D            E
---------------- ----------   ----------  ------------  -----------
2020-02-12              221        68677         99.68          .32
2020-02-12              285       147304         99.81          .19```

When I am trying to take average of column D ie., (99.68+99.81)/2, I am able to fetch some value. But there is a scenario where first row is completely zero as follows.

```A                B                   C            D             E
---------------- ----------  ---------- ------------ ------------
2020-02-12              0             0            0            0
2020-02-12              0        147304          100         0.19```

In this scenario, average of 0,100 is giving me 50% which is incorrect output for my requirement. I have to take average of 0,100. Whenever there is a 0, I should ignore the row and take only the 100 directly. Like AVG(4,2,NULL) gives result as 3. AVG ignores NULL. Similar way, I should ignore the '0' and proceed with average of column D.

Kindly help me achieve the requirement.

and we said...

Use a case expression or similar inside AVG to map zero to null:

```with rws as (
select level - 1 c1 from dual
connect by level <= 5
)
select avg ( c1 ) mean,
avg (
case
when c1 = 0 then null
else c1
end
) non_zero_mean
from   rws;

MEAN       NON_ZERO_MEAN
2              2.5 ```

(1 rating)

Reviews

nullif

February 14, 2020 - 6:21 pm UTC

Reviewer: anton

What about the nullif function? Somthing like
with rws as (
select level - 1 c1 from dual
connect by level <= 5
)
select avg ( c1 ) mean,
avg ( nullif( c1, 0 ) non_zero_mean
from rws;

Followup

February 17, 2020 - 1:46 am UTC

nice input

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.