Re: Calculating Average on columns with zeros.

From: Kevin <kk2796_at_hotmail.com>
Date: 9 Aug 2004 10:55:22 -0700
Message-ID: <5a1dc659.0408090955.5d3dbf1a@posting.google.com>

> > > e.g. if I had
> > > wk1 wk2 wk3
> > > rowA 0 2 4
> > > rowB 0 0 0
> > > rowC 1 0 0
> > >
> > > I am using
> > >
> > > SELECT name, SUM(cash), AVG(cash) from TABLE where cash>0;
> > >
> > > And I get
> > > rowA, 6, 3
> > > rowC, 1, 1
> > >
> > > This is correct for those rows but I want a result for rowB that has
> > >
> > > rowB, 0, 0
> > >
> > > Can anyone help with this.

For the way you describe the situation here, you simply need:

SELECT my_tab.*,

       (wk1+wk2+wk3) /
           DECODE(wk1+wk2+wk3,0,1,
           (DECODE(wk1,0,0,1)+DECODE(wk2,0,0,1)+DECODE(wk3,0,0,1)))
             avg_value,
        (wk1+wk2+wk3) total_value                

FROM my_tab Received on Mon Aug 09 2004 - 12:55:22 CDT