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: [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).
