median function

From: Adams, Matthew (GECP, MABG, 088130) <MATT.ADAMS_at_appl.ge.com>
Date: Tue, 10 Jun 2003 08:54:10 -0700
Message-ID: <F001.005AE502.20030610083947@fatcity.com>

I'm attempting to write a query to calculate the median of a column of numbers.

The first solution I came across was

Select avg(col1) MEDIAN from
( select rownum row1, col1 from a where col1 in (select col1 from a )) a where a.row1 in ( select floor(count(*)/2 +.5) from a )
      or a.row1 in ( select ceil(count(*)/2+.5) from a )

This does too many FT scans (4) of table a, so I tried to write a simpler version using the analytical functions.

I have gotten as far as

 SELECT col1
 FROM
 (
 SELECT col1
 , row_number() OVER (ORDER BY col1) AS r
 , CEIL(COUNT(col1) OVER () /2) m
 FROM a
 )
 WHERE r = m

However, this only works for an odd number of values. IIRC, if an even number of values is present, the median is defined as the average of the two middle-most numbers.

Before I spend much more time on this, has anybody already written one ?

---

Matt Adams - GE Appliances - [EMAIL PROTECTED] If carpenters built buildings the way programmers write programs, the first woodpecker to come along would destroy civilization. - author unknown Received on Tue Jun 10 2003 - 10:54:10 CDT