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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
