Oracle/SQL MEDIAN Function

SQL Median Function returns the median of available values. Oracle Median Function accepts column and formula as parameter for median calculation.

SQL MEDIAN Function Syntax

SELECT MEDIAN(numeric column / formula)
FROM table_name
WHERE conditions;

SQL MEDIAN Function Examples

Suppose we have a table named “Employee” with the data as shown below.

We will see the usage of Oracle MEDIAN Function below.

SQL MEDIAN Function – Simple Usage

The simplest use of Oracle MEDIAN Function would be to calculate MEDIAN of a column.

For example: Oracle MEDIAN query returns the median of salaries from employee table.

SELECT MEDIAN(salary) Median_Salary
FROM employee;

Above mentioned Oracle MEDIAN query returned ‘28000’ as median of salaries.

Note: We have aliased MEDIAN(Salary) as Median_Salary.

SQL MEDIAN Function – Using Formula Example

Oracle MEDIAN Function also accepts formula as parameter.

For example: Oracle MEDIAN query returns median of salaries*(commission/100) from employee table as shown below

SELECT MEDIAN(salary*(commission/100)) Median_Salary_Comm
FROM employee;

Above mentioned SQL MEDIAN query returned ‘3600’ as median of salary*(commission/100).

Note: We have aliased MEDIAN(salary*(commission/100)) as Median_Salary_Comm