Re: computing median

Herwig,
  Try this. Build pl/sql function passing value for col_distvalue around this cursor....This function could be as dynamic as you want. In the loop looking at rows Get the Col1 row with column cume_dist value of .5 it works for even/odd number of total rows. Return the col1 value.

Test data Col_distvalue is varchar2 of the same value e.g. 'X' col1 is number

SELECT col_distvalue,COL1,count(*) count , CUME_DIST() OVER (PARTITION BY col_distvalue ORDER BY col1) AS cume_dist   FROM mediantest
  group by col_distvalue,col1

I was suprised a median function hadn't been written yet either with all the advanced stats for OLAP services in Oracle 8.

This gets it done competely in Oracle enviroment without EXCEL or other products involved and returns it with a select statement.

hth,
Dave Fowler

"Herwig Henseler" <hh_at_ecce-terram.de> wrote in message news:hh-2D0C99.18481320042001_at_news.f.via-net-works.de...
> Hi experts,
>
> [Oracle 8i 8.1.7]
>
> How do I compute the Median of values in a table? I guess this must be a
> FAQ but haven't found an answer so far. The median is the value in the
> "middle" of a sorted list of values. That is, half of all values are
> bigger and the other half is smaller than the median.
>
> TIA,
> Herwig
>
> --
> ECCE TERRAM Internet Services GmbH Dr. Herwig Henseler
> E-Business & Webagentur Tel. 0441 / 500 12-0
> Heinrichstr. 18d Fax. 0441 / 500 12-29
> 26131 Oldenburg URL: http://www.ecce-terram.de
> "Since I've solved all our Y2K problems, I will be on vacation from
> 28th Dec 1999 to 6th Jan 1900."
>
Received on Mon Apr 23 2001 - 13:42:59 CDT