Oracle MEDIAN Function

The average salary of a department rises sharply when one director earns several times what everyone else does. The median does not: it is the middle value, with half the values below and half above. For salaries, prices, durations, and other skewed data, MEDIAN often describes the typical value better than AVG.

Code for This Guide

The main examples are in the examples/aggregate-functions folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

Syntax:

median(expr)

MEDIAN sorts the non-NULL values and returns the middle one, or the average of the two middle values when there is an even number of them. It works on numbers and on dates and timestamps.

Median and Average Salaries

Example:

select department_id, count(*) as staff, median(salary) as median_salary,
       round(avg(salary)) as avg_salary, stats_mode(base_airport) as usual_base
from   employees
where  department_id in (20, 30, 40)
group  by department_id;

Output:

   DEPARTMENT_ID    STAFF    MEDIAN_SALARY    AVG_SALARY USUAL_BASE
________________ ________ ________________ _____________ _____________
              20       12            17000         17817 DXB
              30       11             6900          9009 DXB
              40       10            13400         14210 DXB

In cabin services (department 30), the median salary is 6,900 but the average 9,009: a few highly paid managers pull the average up, while the median shows what a typical crew member earns.

One Outlier

The effect is easiest to see on a small set of values with one outlier.

Example:

-- one very high value pulls the average up, not the median
select avg(v) as average, median(v) as median
from   (values (100), (110), (120), (130), (5000)) t (v);

Output:

   AVERAGE    MEDIAN
__________ _________
      1092       120

One value of 5,000 lifts the average to 1,092, nearly ten times the typical value, while the median stays at 120.

Things to Know

• MEDIAN is PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY expr), and gives the same result.
• It works as an analytic function too: MEDIAN(salary) OVER (PARTITION BY department_id).
• On very large tables, APPROX_MEDIAN estimates the median much faster.

Related Guides

• Oracle AVG Function

Conclusion

MEDIAN returns the middle value of a group, averaging the two middle values for an even count. Unlike AVG, it is not pulled by outliers, which makes it the better summary of skewed data such as salaries and prices.