1 Reply Latest reply: Mar 5, 2013 12:27 PM by Solomon Yakobson RSS

    select with average cal

    user9542267
      Hi All,
      I have 3 fields with number values in a table.
      How can I do the average not counting for values with zero?
      Example:
      10 20 30 = 60/3 = 20
      10 0 20 = 30/2 = 15 --divided by two because one of the values is zero.
      0 6 0 = 6/1 = 6
      I can probably do a long case statement but i wonder if it is a better way.
      Thanks in advanced
        • 1. Re: select with average cal
          Solomon Yakobson
          with t as (
                     select 10 col1,20 col2,30 col3 from dual union all
                     select 10,0,20 from dual union all
                     select 0,6,0 from dual
                    )
          select  (nvl(col1,0) + nvl(col2,0) + nvl(col3,0)) / (sign(abs(nvl(col1,0))) + sign(abs(nvl(col2,0))) + sign(abs(nvl(col3,0)))) avg
            from  t
          /
          
                 AVG
          ----------
                  20
                  15
                   6
          SQL> 
          SY.