# Re: Calculating Average on columns with zeros.

Date: 10 Aug 2004 05:07:33 -0700

Message-ID: <c5972bdf.0408100407.4423c33d_at_posting.google.com>

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