Re: Calculating Average on columns with zeros.

Hi Peter,

Try this!
will work out OK I think...

Select Sum(Cash) / Sum(Case When Cash > 0 Then 1 Else 0 End)   From Table

cheers, Adem

ed.prochak_at_magicinterface.com (Ed prochak) wrote in message news:<4b5394b2.0408050823.4de2bb81_at_posting.google.com>...
> peterhardy_at_f2s.com (PeterHardy) wrote in message news:<17736c9c.0408050049.565668e5_at_posting.google.com>...
> > I'm trying to work out an average field on a report that i'm writing
> > and having the problem described below.
> >
> > e.g. if you had 3 weeks of cash which were 0, 2, 4 then a normal
> > average
> > would be ((0+2+4)/3) = 2 but the average that I want is (2+4)/2 = 3.
> >
> > Now I can do this in SQL by using AVG(cash) with a WHERE cash>0. But
> > the
> > trouble comes when all cash values are zero for all all columns on the
> > report.
> >
> > 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.
>
>
> you'll have to special case that condition. something like this should do it:
> SELECT name, SUM(cash), 0 FROM table GROUP BY name having SUM(cash) = 0;
>
> then you just need a UNION.
>
> HTH,
> Ed
Received on Tue Aug 10 2004 - 14:07:33 CEST