using median function in oracle database query

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.

Employee_Id	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
104	Emp D	30000	Support
105	Emp E	32000	Sales	10

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