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