Re: Mean/Median

From: Jared Still <jkstill_at_cybcon.com>
Date: Tue, 15 May 2001 15:46:00 -0700
Message-ID: <F001.00304572.20010515154611@fatcity.com>

On Tuesday 15 May 2001 12:00, Eric D. Pierce wrote:

> I'm not familiar with that syntax, is it one of the 9i
> self-tuning, self-managing feathures?
>

Hmm..

It appeared properly on the list, must have been munged somewhere along the line.

Here 'tis again.

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: Jared Still
  INET: jkstill_at_cybcon.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 Tue May 15 2001 - 17:46:00 CDT