| Need help with averages across the columns [message #517396] |
Mon, 25 July 2011 12:44  |
aarti81
Messages: 235
Registered: December 2007
Location: USA
|
Senior Member |
|
|
Hi All
I have the following 6 columns in a report. And i need a 7th column which is a calculated field (average of the first 6 columns), the problem is some of the records have a '0' value in them so the aveage is not always (SUM)/6, If one of the fields have a 0 then the average is (SUM)/5, likewise if 2 fields have o's in them then the average will be (SUM)/4, Please need help, please see the sample data set.
A B C D E F G(Average)
83 83 33 0 0 100
83 83 33 0 0 100
0 67 67 100 17 92
83 83 100 67 50 100
83 83 100 83 50 100
67 0 83 100 17 100
83 83 83 67 67 100
83 83 83 50 50 90
67 100 67 0 33 42
83 83 0 67 67 26
0 83 83 67 0 34
83 83 83 67 67 42
83 83 83 50 17 100
83 83 83 100 50 100
100 0 100 0 0 18
0 83 83 50 50 92
67 83 83 67 67 92
83 100 83 33 33 34
|
|
|
|
|
|
|
|
|
|
| Re: Need help with averages across the columns [message #517409 is a reply to message #517406] |
Mon, 25 July 2011 15:11  |
aarti81
Messages: 235
Registered: December 2007
Location: USA
|
Senior Member |
|
|
Thanks littlefoot,
But the thing is its user requirement, i asked them the same thing and the data is related to scores, the 6 fields represent student scores in different subjects and '0' in any one of the field means that student didnt registered for the course so if a student enrolled in 3 subjects only then only 3 fields are populated and rest 3 fields are '0'. So their average is sum of all including 0's across the 6 fields divided by 3.
Thanks
|
|
|
|
| Re: Need help with averages across the columns [message #517414 is a reply to message #517408] |
Mon, 25 July 2011 21:40 |
averion
Messages: 42
Registered: January 2009
Location: US
|
Member |
|
|
Try this,
(Column1+Column2+Column3+Column4)/
Decode(Column1,0,0,1)+Decode(Column2,0,0,1)+Decode(Column3,0,0,1)+Decode(Column4,0,0,1)
This should produce correct result.
|
|
|
|
|
|
| Re: Need help with averages across the columns [message #517561 is a reply to message #517465] |
Tue, 26 July 2011 11:09 |
aarti81
Messages: 235
Registered: December 2007
Location: USA
|
Senior Member |
|
|
Thanks all but i tried something like this and it worked.
I tried a different way, using the CASE statement i created 6 different calculations.
CASE when columna='0' then count(columnA) else '0' end, so i'm getting the count per each column if there is a '0' else it will give me a '0' then i summed them up as sum of counts and created a calculation whichi subtract this sum from 6 as there are 6 columns and i'm using this as a DENOMINATOR in calculating the average.
Thanks again for your time.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
