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.

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