Median Analytic Functions in Oracle SQL

MEDIAN
MEDIAN function is fetch the middle value from set of sorted data. The “median” is the “middle” value in the list of numbers.
NULL value is ignored in the calculation.

ODD n number of rows: Arrange number from smaller to higher and then Median is exactly the middle number.
EVEN n number of rows: Arrange number from smaller to higher and Median is calculated result value as sum of two middle numbers divide by 2.

Syntax: MEDIAN(expr) [ OVER (query_partition_clause) ]

Examples of Median function:

Fetch the median salary for each department in the hr.employees table

SELECT department_id, MEDIAN(salary)
FROM employees
GROUP BY department_id
ORDER BY department_id;

DEPARTMENT_ID MEDIAN(SALARY)
------------- --------------
           10           4400
           20           9500
           30           2850
           40           6500
           50           3100
           60           4800

Finding department wise median salary with employee detail

SELECT department_id,employee_id, salary,
MEDIAN(salary) OVER (PARTITION BY department_id) "Median by department"
FROM employees
-- WHERE department_id > 60
ORDER BY department_id, salary;

DEPARTMENT_ID EMPLOYEE_ID     SALARY Median by department
------------- ----------- ---------- --------------------
           10         200       4400                 4400
           20         202       6000                 9500
           20         201      13000                 9500
           30         119       2500                 2850
           30         118       2600                 2850
           30         117       2800                 2850
           30         116       2900                 2850
           30         115       3100                 2850
           30         114      11000                 2850
           40         203       6500                 6500