Calculating Average on columns with zeros.

[PeterHardy]

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.

[Mark D Powell]

pe********@f2s.com (PeterHardy) wrote in message news:<17**************************@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.

Peter, the SQL you posted calculates the sum for a column on rows of
data while the description is summing the columns on a single row.
Which is it that you actually want? [A + B + C or summation of A for
the row set]

HTH -- Mark D Powell --

[Ed prochak]

pe********@f2s.com (PeterHardy) wrote in message news:<17**************************@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

[PeterHardy]

Ma*********@eds.com (Mark D Powell) wrote in message news:<26**************************@posting.google. com>...

pe********@f2s.com (PeterHardy) wrote in message news:<17**************************@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.

Peter, the SQL you posted calculates the sum for a column on rows of
data while the description is summing the columns on a single row.
Which is it that you actually want? [A + B + C or summation of A for
the row set]

HTH -- Mark D Powell --

It is a summation of the row for the row set that i require.

[Kevin]

> > > 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

[Adem]

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********@magicinterface.com (Ed prochak) wrote in message news:<4b**************************@posting.google. com>...

pe********@f2s.com (PeterHardy) wrote in message news:<17**************************@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