SQL> select PERCENTILE_CONT(0.5) 2 within group (ORDER BY sal DESC) sal 3 from emp;
SAL ---------- 1550 hth connor --- Ben <[EMAIL PROTECTED]> wrote: > median functionhere is a note from the list from > awhile aback: > > Since this subject was brought back up, I thought > maybe some would be > interested in the following. I've never had a need > to calculate a median, > but, I knew Celko's SQL for Smarties had a few > variations and examples from > various people, each with caveats. And then there > were differences between > what he termed statistical and financial mean, and > some other things as > well. Anyway, a google search turned up another > Celko solution. And this one > also brings up the concept of weighted median. > > Here is his example, > http://www.intelligententerprise.com/db_area/archives/1999/992004/celko.shtm > l , > modified by me to use the standard EMP table's SAL > column: > > SQL> SELECT AVG(DISTINCT x.sal) > 2 FROM (SELECT F1.sal > 3 FROM emp F1, emp F2 > 4 GROUP BY F1.empno, F1.sal > 5 HAVING SUM(CASE WHEN F2.sal = F1.sal > 6 THEN 1 ELSE 0 END) > 7 >= ABS(SUM(CASE WHEN F2.sal < F1.sal > THEN 1 > 8 WHEN F2.sal > F1.sal > THEN -1 > 9 ELSE 0 END))) > 10 X > 11 / > > AVG(DISTINCTX.SAL) > ------------------ > 1550 > > The link above goes into some detail regarding the > logic behind the query > and how his query finally reached the form above. I > may never need to do a > median, but, this subject has been a good > opportunity for learning. I've > tested the above with even, odd, multiple occurences > of SAL, null,s etc. It > seems to work, but, everyone have a whack at it if > you like. > > Regards, > > Larry G. Elkins > [EMAIL PROTECTED] > -----Original Message----- > Adams, Matthew (GECP, MABG, 088130) > Sent: June 10, 2003 12:40 PM > To: Multiple recipients of list ORACLE-L > > > I'm attempting to write a query to calculate the > median > of a column of numbers. > > The first solution I came across was > > Select avg(col1) MEDIAN from > ( select rownum row1, col1 from a where col1 in > (select col1 from a )) a > where a.row1 in ( select floor(count(*)/2 +.5) from > a ) > or a.row1 in ( select ceil(count(*)/2+.5) from > a ) > > This does too many FT scans (4) of table a, so I > tried to write > a simpler version using the analytical functions. > > I have gotten as far as > > SELECT col1 > FROM > ( > SELECT col1 > , row_number() OVER (ORDER BY col1) AS r > , CEIL(COUNT(col1) OVER () /2) m > FROM a > ) > WHERE r = m > > However, this only works for an odd number of > values. > IIRC, if an even number of values is present, the > median > is defined as the average of the two middle-most > numbers. > > Before I spend much more time on this, has anybody > already written > one ? > > ---- > Matt Adams - GE Appliances - [EMAIL PROTECTED] > If carpenters built buildings the way programmers > write > programs, the first woodpecker to come along would > destroy > civilization. - author unknown > ===== Connor McDonald web: http://www.oracledba.co.uk web: http://www.oaktable.net email: [EMAIL PROTECTED] "GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day" __________________________________________________ Yahoo! Plus - For a better Internet experience http://uk.promotions.yahoo.com/yplus/yoffer.html -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).