RE: sql/function to calculate the median

From: MacGregor, Ian A. <ian_at_SLAC.Stanford.EDU>
Date: Tue, 22 Apr 2003 14:43:00 -0800
Message-ID: <F001.005877CC.20030422144300@fatcity.com>

I had asked the median question over a year ago posting a proposed solution and asking for better methods. A few people psoted a solution out of Celko's book. However, testing that solution showed that it did not always give the right answer. Larry Elkins came up with the solution which costs least to run. But I have misplaced it.

Ian MacGregor
Stanford Linear Accelerator Center
ian_at_SLAC.Stanfoprd.edu

-----Original Message-----

Sent: Tuesday, April 22, 2003 12:32 PM
To: Multiple recipients of list ORACLE-L

(Since I've not seen any responses to this I'll give it a go.)

If you can use OLAP functions, have a look at PERCENTILE_CONT--that gives you any percentile you need--median is of course 50th.

There is a nice discussion of this & several candidate solutions in Celko's _SQL For Smarties_.

I was thinking you might be able to do something quick-n-dirty by counting the number of non-null values in the column whose median you need & then selecting out the value that falls at rownum = round(count/2,0) in a properly ordered result set. But I couldn't get that to work on a test table, so I won't post my code.

HTH,
-Roy

Roy Pardee
Programmer/Analyst
SWFPAC Lockheed Martin IT
Extension 8487

-----Original Message-----

Sent: Monday, April 21, 2003 12:12 PM
To: Multiple recipients of list ORACLE-L

Hi All,

Oracle 8.0.5
Does anyone have sql statement/functin to calculate the median?

Thanks
Rick

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author:
  INET: Rick_Cale_at_teamhealth.com

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: ListGuru_at_fatcity.com (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).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Pardee, Roy E
  INET: roy.e.pardee_at_lmco.com

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: ListGuru_at_fatcity.com (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).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: MacGregor, Ian A.
  INET: ian_at_SLAC.Stanford.EDU

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: ListGuru_at_fatcity.com (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). Received on Tue Apr 22 2003 - 17:43:00 CDT