Home » SQL & PL/SQL » SQL & PL/SQL » Average in Analytical function
Average in Analytical function [message #231336] Mon, 16 April 2007 10:30 Go to next message
marks20101
Messages: 74
Registered: May 2005
Member
SELECT process_run_pid,
sum(count(process_run_pid)) over (partition by process_run_pid order by process_run_pid) total
FROM err_log
WHERE TRUNC(ERR_TS) = TO_DATE('13-APR-07','DD-MON-YY')
GROUP BY process_run_pid;

i.e.
results:
53389 25075 80%
53397 19 03%
53399 820 05%
53412 326 ........ect...
53413 4
53414 2073

I want to get the average per totals. I'm not sure how because the total is not an actual column.
Re: Average in Analytical function [message #231349 is a reply to message #231336] Mon, 16 April 2007 12:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/
You can't get the result you posted with the query you posted. How can we help you if you post wrong things.

2/
You can get average with avg function.
It is really not clear average of what you want.

Regards
Michel
Re: Average in Analytical function [message #231356 is a reply to message #231336] Mon, 16 April 2007 12:58 Go to previous messageGo to next message
marks20101
Messages: 74
Registered: May 2005
Member
The query is what need to get:
53389 25075
53397 19
53399 820
53412 326
53413 4
53414 2073

Thats okay.

I want to add the percentages of the totals to this query. Sorry if I was not clear.

53389 25075 80%
53397 19 08%
53399 820 ..........
53412 326
53413 4
53414 2073

Thanks,
MS
Re: Average in Analytical function [message #231358 is a reply to message #231356] Mon, 16 April 2007 13:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
select process_run_pid, total,
100*ratio_to_report(total) over () pct
from (
SELECT process_run_pid,
sum(count(process_run_pid)) over (partition by process_run_pid order by process_run_pid) total
FROM err_log
WHERE TRUNC(ERR_TS) = TO_DATE('13-APR-07','DD-MON-YY')
GROUP BY process_run_pid
)
/

Regards
Michel

Re: Average in Analytical function [message #232480 is a reply to message #231336] Fri, 20 April 2007 12:36 Go to previous message
marks20101
Messages: 74
Registered: May 2005
Member
Thanks Mike. Seems like I learned two things from this query.
Previous Topic: DDL in function
Next Topic: spool to file using a plsql block
Goto Forum:
  


Current Time: Fri Dec 09 21:19:12 CST 2016

Total time taken to generate the page: 0.08266 seconds