Re: Calculating Average on columns with zeros.

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 Thu Aug 05 2004 - 18:23:11 CEST