Calculating Average on columns with zeros.
From: PeterHardy <peterhardy_at_f2s.com>
Date: 5 Aug 2004 01:49:24 -0700
Message-ID: <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.
Date: 5 Aug 2004 01:49:24 -0700
Message-ID: <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
I am using
SELECT name, SUM(cash), AVG(cash) from TABLE where cash>0;
And I get
rowA 0 2 4
rowB 0 0 0
rowC 1 0 0
rowA, 6, 3
This is correct for those rows but I want a result for rowB that has
rowB, 0, 0
Can anyone help with this. Received on Thu Aug 05 2004 - 10:49:24 CEST