Oracle® Database SQL Reference 10g Release 1 (10.1) Part Number B1075901 


View PDF 
MEDIAN
is an inverse distribution function that assumes a continuous distribution model. It takes a numeric or datetime value and returns the middle value or an interpolated value that would be the middle value once the values are sorted. Nulls are ignored in the calculation.
This function takes as arguments any numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype. If you specify only expr
, then the function returns the same datatype as the numeric datatype of the argument. if you specify the OVER
clause, then Oracle Database determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that datatype, and returns that datatype.
See Also: Table 211, "Implicit Type Conversion Matrix" for more information on implicit conversion and "Numeric Precedence " for information on numeric precedence 
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
).
The final result will be:
if (CRN = FRN = RN) then (value of expression from row at RN) else (CRN  RN) * (value of expression for row at FRN) + (RN  FRN) * (value of expression for row at CRN)
You can use MEDIAN
as an analytic function. You can specify only the query_partition_clause
in its OVER
clause. It returns, for each row, the value that would fall in the middle among a set of values within each partition.
Compare this function with these functions:
PERCENTILE_CONT , which returns, for a given percentile, the value that corresponds to that percentile by way of interpolation. MEDIAN
is the specific case of PERCENTILE_CONT
where the percentile value defaults to 0.5.
PERCENTILE_DISC , which is useful for finding values for a given percentile without interpolation.
The following query returns the median salary for each department in the hr.employees
table:
SELECT department_id, MEDIAN(salary) FROM employees GROUP BY department_id; DEPARTMENT_ID MEDIAN(SALARY)   10 4400 20 9500 30 2850 40 6500 50 3100 60 4800 70 10000 80 8900 90 17000 100 8000 110 10150 7000