Re: median value

From: Lisa Kilroy <lkilroy_at_infoseek.com>
Date: 1996/12/12
Message-ID: <32B0A059.4F33_at_infoseek.com>#1/1


[Quoted] If you have an even number of records you are supposed to avg. those two middle values.

Here's the final function. (except I would like to figure out how to make table independent. - Any ideas?)

create or replace
function median_x (dm_trans IN number)
return number is
 w_count number;
 medianval dtest.dm_trans%type;
begin
 select count(*) into w_count from dtest;  if mod(w_count,2) = 0 then
  select avg(dm_trans) into medianval
  from dtest t where w_count/2 in
   (select count(*) from dtest where dm_trans <=t.dm_trans)     or (w_count/2)+1 = (select count(*) from dtest      where dm_trans <=t.dm_trans);
 else
 select dm_trans into medianval
 from dtest t where floor((w_count/2)+1) =    (select count(*) from dtest where dm_trans <=t.dm_trans);   end if;
 return (medianval);
end ;
/

Ken Denny wrote:
>
> 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 */
> return medianval;
> end;
> /
>
> Ken Denny
> Insight Industries, Inc.
> RTP, NC
> kdenny_at_interpath.com
Received on Thu Dec 12 1996 - 00:00:00 CET

Original text of this message