Home » Developer & Programmer » Reports & Discoverer » Need help with averages across the columns (discoverer plus 10.1.2)
Need help with averages across the columns [message #517396] Mon, 25 July 2011 12:44 Go to next message
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 
Thanks


Re: Need help with averages across the columns [message #517404 is a reply to message #517396] Mon, 25 July 2011 14:37 Go to previous messageGo to next message
averion
Messages: 42
Registered: 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 Go to previous messageGo to next message
Littlefoot
Messages: 19294
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account 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 Go to previous messageGo to next message
aarti81
Messages: 235
Registered: 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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #517465 is a reply to message #517414] Tue, 26 July 2011 03:51 Go to previous messageGo to next message
cookiemonster
Messages: 10841
Registered: 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 Go to previous message
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.






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


Current Time: Tue Jul 22 12:33:42 CDT 2014

Total time taken to generate the page: 0.27547 seconds