From: Ken Denny <kdenny@interpath.com>
Subject: Re: median value
Date: 1996/12/12
Message-ID: <32B01BFE.3D4E@interpath.com>#1/1
references: <32AF39B2.1759@infoseek.com> <32AFFAAF.342@adra.se>
to: Roger Wernersson <roger.wernersson@adra.se>
cc: lkilroy@infoseek.com
content-type: text/plain; charset=us-ascii
organization: Interpath
mime-version: 1.0
newsgroups: comp.databases.oracle.tools
x-mailer: Mozilla 2.02 (X11; I; HP-UX A.09.05 9000/715)



Roger Wernersson wrote:
> 
> Lisa Kilroy wrote:
> > I need to pull the median value out of a table for a particular field.
> 
> Hello Lisa, no problem!
> 
> SELECT MAX (value)
> FROM table
> WHERE value <= (
>   SELECT AVG (value)
>   FROM table
> )
> 
> This might not be the fastest way but it works.
> 
> --
> Sport radio: people listening to people watching people having fun
> Mailto:roger.wernersson@adra.se
> BTW: All opinions are mine, all mine, and nobody's but mine.

Except that that's not the median. If our table had values 1,2,3,24, and
100 then your solution would yield 24 when the median is really 3.
There's no easy way to do it. You have to do a select count(*) from
table then find the value where half the count is less than or equal.

create or replace
function median_x return number is /* assuming value is a number */
declare
   w_count    number;
   medianval  table.value%type;
begin
   select count(*) into w_count from table;
   select value into medianval from table t where floor(w_count/2) =
      (select count(*) from table where value <=t.value)
      and rownum=1; /* in case more than one row has the median value */
   return medianval;
end;
/

Ken Denny
Insight Industries, Inc.
RTP, NC
kdenny@interpath.com