Home » Developer & Programmer » Reports & Discoverer » Need help with averages across the columns (discoverer plus 10.1.2)
Need help with averages across the columns Mon, 25 July 2011 12:44
 aarti81 Messages: 235Registered: 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 ```
Thanks

Re: Need help with averages across the columns [message #517404 is a reply to message #517396] Mon, 25 July 2011 14:37
 averion Messages: 42Registered: January 2009 Location: US Member
Use Case statements in both cases.Something like,

Sum(case when 'column'>1 then 'Column' else 0 end)/
Sum(Case when 'column'>1 then 1 else 0 end)

First part should add totals and the second part should correctly calculate Divider.
Re: Need help with averages across the columns [message #517406 is a reply to message #517396] Mon, 25 July 2011 14:41
 Littlefoot Messages: 21101Registered: June 2005 Location: Croatia, Europe Senior MemberAccount Moderator
Last month my salary was \$1000.
This month my salary was \$0.

You say that my average salary (regarding June and July) is \$1000.

I don't think so.

What kind of a request is that? Doesn't make much sense.

Anyway, that was just me thinking aloud. I don't know Discoverer and can't tell whether you can (or can not) do that, but it smells like you'll have to create your own "AVG_AARTI" function.
Re: Need help with averages across the columns [message #517408 is a reply to message #517404] Mon, 25 July 2011 14:56
 aarti81 Messages: 235Registered: December 2007 Location: USA Senior Member
Thanks Averion,
But i didnt quiet understand it, i have 6 columns and the CASE statement is per each column? so i should create 6 calculations?

Thanks
Re: Need help with averages across the columns [message #517409 is a reply to message #517406] Mon, 25 July 2011 15:11
 aarti81 Messages: 235Registered: 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: 42Registered: 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 #517465 is a reply to message #517414] Tue, 26 July 2011 03:51
 cookiemonster Messages: 12742Registered: September 2008 Location: Rainy Manchester Senior Member
Use nullif to convert the 0's to null. Oracle ignores null for average calculations.
Re: Need help with averages across the columns [message #517561 is a reply to message #517465] Tue, 26 July 2011 11:09
 aarti81 Messages: 235Registered: 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.

 Previous Topic: Displaying One Record per page for all retrieved records Next Topic: Issue to connect to remote database
Goto Forum:

Current Time: Thu May 25 03:45:35 CDT 2017

Total time taken to generate the page: 0.24901 seconds