Oracle/SQL MEDIAN Function

Oracle MEDIAN FunctionSQL 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

Leave a Reply

Your email address will not be published. Required fields are marked *