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