MEDIAN

Purpose

MEDIAN is an inverse distribution function. In contrast to the average function, the median function returns the middle value or an interpolated value from a sorted window or group of rows. NULL values are ignored.

The following formula is evaluated:

Usage Notes

• MEDIAN(expr) is an alias for PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY expr).
• For inverse distribution functions, refer to PERCENTILE_CONT and PERCENTILE_DISC.
• MEDIAN does not allow the use of a window_frame_clause. It uses the entire partition as window for the computation of the result.

• Specifying ALL has no effect since it is already used by default.

• The over_clause must not contain an order_clause.
• For information about the over_clause and analytic functions in general, refer to the Analytic Functions section.

Examples

Aggregate Function

SELECT 
    department, 
   MEDIAN(age) MEDIAN_AGE 
FROM employee_table GROUP BY department;

Result

Analytic Function

SELECT 
    id, department, age, 
    MEDIAN(age) OVER (PARTITION BY department) MEDIAN_AGE 
FROM employee_table ORDER BY department;

Result