Max,Min,Average,Standard Deviation,Median Sql Query Oracle

I have many columns in the table,want to select distinct combination of Col A and Col B and Col C and peform calculation in Col D for each distinct combination of Col A,Col B and Col C.and calculate in Col C(Max,Min,Average,Median and Standard Deviation) Values.Please let me know how to write a query for it.

Ex is attached.

Template.xls
zonate_toasterAsked:
Who is Participating?
 
slightwv (䄆 Netminder) Commented:
You need to 'group' all non-aggregate columns.

You need to add:
select ...
from ...
group by hostname,instance,order_type
order by hostname;

>>Please help me get the function for median
It exists in 10g and above I think.  Not sure what version that link referenced.  When in doubt:  Go to the online docs for your version.

11g:
http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/functions097.htm#SQLRF06315

Depending you your exact requirements look at the 'OVER' property on these functions.  The anayltic window functions are pretty powerful.

0
 
slightwv (䄆 Netminder) Commented:
Check out the analytic functions:

http://psoug.org/reference/analytic_functions.html

If you need help with any syntax given the data you provided, what are your expected results?
0
 
zonate_toasterAuthor Commented:
What you told me i did that:-
select distinct hostname,instance,order_type,max(latency_micro_sec) as Max_Latency,min(latency_micro_sec) as Min_Latency,avg(latency_micro_sec) as Aver_Latency,
stddev(latency_micro_sec) as Stddev_Latency from latency_info order by hostname;

Please help me get the function for median also,and when i execute this command i get not a single-group group function.Please help me to write the query.
0
 
sdstuberCommented:
prior to 10g there wasn't a MEDIAN function but you can simulate it with

 PERCENTILE_DISC(.50)

 SELECT hostname,
         instance,
         order_type,
         MAX(latency_micro_sec) AS max_latency,
         MIN(latency_micro_sec) AS min_latency,
         AVG(latency_micro_sec) AS aver_latency,
         STDDEV(latency_micro_sec) AS stddev_latency,
         PERCENTILE_DISC(.50) WITHIN GROUP (ORDER BY latency_micro_sec)
             median_latency
    FROM latency_info
GROUP BY hostname, instance, order_type
ORDER BY hostname;

Open in new window

0
 
zonate_toasterAuthor Commented:
TThank your for your prompt reply.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.