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]

Re: Calculating Average on columns with zeros.

peterhardy@f2s. com (PeterHardy) wrote in message news:<17736c9c. 0408050049.5656 68e5@posting.go ogle.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]

Re: Calculating Average on columns with zeros.

peterhardy@f2s. com (PeterHardy) wrote in message news:<17736c9c. 0408050049.5656 68e5@posting.go ogle.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]

Re: Calculating Average on columns with zeros.

Mark.Powell@eds .com (Mark D Powell) wrote in message news:<2687bb95. 0408050607.2cd6 a38a@posting.go ogle.com>...

peterhardy@f2s. com (PeterHardy) wrote in message news:<17736c9c. 0408050049.5656 68e5@posting.go ogle.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]

Re: Calculating Average on columns with zeros.

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(w k3,0,0,1)))
avg_value,
(wk1+wk2+wk3) total_value
FROM my_tab

[Adem]

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@magi cinterface.com (Ed prochak) wrote in message news:<4b5394b2. 0408050823.4de2 bb81@posting.go ogle.com>...

peterhardy@f2s. com (PeterHardy) wrote in message news:<17736c9c. 0408050049.5656 68e5@posting.go ogle.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