 | |
Calculating Average on columns with zeros.
comp.databases.oracle comp.databases.oracle
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Calculating Average on columns with zeros. -
08-05-2004
, 03:49 AM
#2
| |||
| |||
|
|
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. |
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 --
#3
| |||
| |||
|
|
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
#4
| |||
| |||
|
|
peterhardy (AT) f2s (DOT) com (PeterHardy) wrote in message news:<17736c9c.0408050049.565668e5 (AT) posting (DOT) 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.
#5
| |||
| |||
|
|
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. |
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
#6
| |||
| |||
|
|
peterhardy (AT) f2s (DOT) com (PeterHardy) wrote in message news:<17736c9c.0408050049.565668e5 (AT) posting (DOT) 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 |
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.