Re: median value

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_at_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 */

Ken Denny
Insight Industries, Inc.
RTP, NC
kdenny_at_interpath.com Received on Thu Dec 12 1996 - 00:00:00 CET