Re: Mean/Median

From: Jared Still <jkstill_at_cybcon.com>
Date: Mon, 14 May 2001 22:38:22 -0700
Message-ID: <F001.003033DC.20010514221520@fatcity.com>

On Monday 14 May 2001 19:55, Pat Hildebrand wrote:
> Would depend on just what you are looking for. A quick look at
> Oracle's SQL functions showed avg, st. deviation, and variance
> although no median but that should be easier to write than some of the
> others.
>
> Pat

Ha!

Here's the script I have for median. Feel free to simplify it. :)

And if you do, I want a copy.

I can't claim authorship of this, I borrowed it from somewhere.

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
        )

)
/

Aˤ

-- 
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 - 00:38:22 CDT