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