Oracle Database Statistical functions Calculating the median of a set of values
Example
The MEDIAN function since Oracle 10g is an easy to use aggregation function:
SELECT MEDIAN(SAL)
FROM EMP
It returns the median of the values
Works on DATETIME values too.
The result of MEDIAN is computed by first ordering the rows. Using N as the number of rows in the group, Oracle calculates the row number (RN) of interest with the formula RN = (1 + (0.5*(N-1)). The final result of the aggregate function is computed by linear interpolation between the values from rows at row numbers CRN = CEILING(RN) and FRN = FLOOR(RN).
Since Oracle 9i you can use PERCENTILE_CONT which works the same as MEDIAN function with percentile value defaults to 0.5
SELECT PERCENTILE_CONT(.5) WITHIN GROUP(order by SAL)
FROM EMP
