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

Breadcrumb

Question and Answer

Chris Saxon

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

You Asked

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 

and you rated our response

  (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;
Connor McDonald

Followup  

February 17, 2020 - 1:46 am UTC

nice input

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.