Re: Median Function

In article <20000304010047.03253.00000149_at_ng-fl1.aol.com>,   hrrascal_at_aol.com (HRrascal) wrote:
> Can anyone help me with a fubction that will return the median of a
group of
> numbers? Thanks for any help.
>
> E Parbuoni
> eparbuon_at_cvm.fda.gov
>

Here are 2 examples. the first query works in all releases (7.1 and up). the second (little more efficient) works in 8.1.5 and up only.

create table t as select user_id from all_users where rownum < 11;

create or replace view number_data as select user_id datum from t /
SELECT AVG(DISTINCT DATUM)
  FROM (SELECT CP1.DATUM

           FROM NUMBER_DATA CP1, NUMBER_DATA CP2
          GROUP BY CP1.DATUM
         HAVING SUM(DECODE(CP1.DATUM, CP2.DATUM, 1, 0)) >=
                        ABS(SUM(SIGN(CP1.DATUM - CP2.DATUM))))

 select avg( user_id )
   from ( select user_id, rownum r

            from ( select user_id
                     from t
                    order by user_id )
        ),
        ( select count(*) cnt
            from T
        )
  where ( cnt/2 = trunc(cnt/2) AND ( r = cnt/2 or r = cnt/2+1 ) )
     or ( cnt/2<> trunc(cnt/2) and ( r = ceil(cnt/2) ) )

--
Thomas Kyte                              tkyte_at_us.oracle.com
Oracle Service Industries
http://osi.oracle.com/~tkyte/index.html
--
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.