Re: Calculating of the Median of Data

From: <Jared.Still_at_radisys.com>
Date: Fri, 22 Jun 2001 09:24:19 -0700
Message-ID: <F001.00333D48.20010622090555@fatcity.com>

> Does anyone have a handy function for calculating the Median of data ?? It
> seems like it should be simple ...but

Kevin,

Maybe not as simple as you think. :)

Here's an example in SQL. You may want to dig through the archives, as this was discussed at some lenght a few months ago.

Ross may remember it.

Jared

• middle value is chosen as median when there is an
• odd number of values
• average of 2 middle values is chosen median
• when there is an even number of values

drop table median;

create table median ( value number );

insert into median values ( 111 );
insert into median values ( 543 );
insert into median values ( 566 );
insert into median values ( 643 );
insert into median values ( 456 );
insert into median values ( 98 );
insert into median values ( 877 );
insert into median values ( 867 );
insert into median values ( 687 );
insert into median values ( 6886 );

• uncomment this row for an odd number of values
  --insert into median values ( 982 );
  

commit;

break on report
compute avg label 'Median' of value on report column value heading 'values|averaged|in median'

select

     rownum,
     value
from (
     select value
     from median
     where value is not null
     union
     select 1 from dual where 1=2

)
group by value, rownum
having rownum >= (

     select decode( mod(total_freq,2),
          1,trunc(total_freq/2 + 1),
          0,trunc(total_freq/2)
     )
     from (
          select count(*) total_freq
          from median
          where value is not null
     )

)
and rownum <= (

     select decode( mod(total_freq,2),
          1,trunc(total_freq/2 + 1),
          0,trunc(total_freq/2 + 1)
     )
     from (
          select count(*) total_freq
          from median
          where value is not null
     )

)
/

--

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

Author:
  INET: Jared.Still_at_radisys.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


--------------------------------------------------------------------

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 Fri Jun 22 2001 - 11:24:19 CDT