A department with nine employees earning $60k and one outlier earning $600k has an average salary pulled sharply upward by that one row, nowhere near what a typical employee there actually earns. The median, the middle value once sorted, isn't affected by that single outlier the way AVG() is.
Learning Objectives
- Define the median, and explain why it resists outliers in a way AVG() doesn't.
- Use Oracle's dedicated MEDIAN() aggregate, the one database here that actually has one as a plain GROUP BY function.
- Know the real, genuinely different approach every other database needs instead, including the two with no built-in path at all.
Sort the Group
Every value, smallest to largest
Find the Middle
Average of the two middle values if the count is even
Median
Resistant to a single extreme outlier
Oracle: a Real, Dedicated Aggregate
Oracle's MEDIAN() behaves like any other plain aggregate function, used directly with GROUP BY, no special clause needed. It's the only database in this course with that.
SELECT MEDIAN(salary) AS median_salary FROM employees WHERE department_id = 3;
Everyone Else's Real Alternative
| Database | Finding the Median |
|---|---|
| PostgreSQL | PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY col), a plain GROUP BY aggregate, no window-function syntax needed. |
| SQL Server | PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY col) OVER (), but only as a window function, SQL Server has no plain-aggregate form of it at all, every row in the partition gets the same repeated value back, needs DISTINCT or an outer GROUP BY to collapse to one row per group. |
| MariaDB (10.3.3+) | MEDIAN(col) OVER (), MariaDB does have its own native MEDIAN(), but only as a window function requiring OVER(), the same per-row-repeated shape as SQL Server's PERCENTILE_CONT. |
| MySQL / SQLite | No built-in median function or percentile function of any kind, needs a manual approach: sort the values, then pick (or average) the middle row(s) by position. |
MariaDB's MEDIAN() is a real, genuine function, just structurally a window function rather than a plain aggregate like Oracle's identically-named one, easy to assume they behave the same way since the name matches exactly.
Try It Yourself
Find the median salary in department id 3, on PostgreSQL.
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median_salary FROM employees WHERE department_id = 3;
Frequently Asked Questions
What does PERCENTILE_CONT(0.5) actually mean?
The 50th percentile, interpolated continuously between the two middle values when the group's count is even, exactly what a median is defined as. 0.5 is specifically the median, other values (like 0.9 for the 90th percentile) are covered in the next lesson.
Why does SQL Server's PERCENTILE_CONT repeat the same value on every row, instead of returning just one?
Because it's structurally a window function there, OVER() is mandatory, and a window function always returns one output row per input row by design, DISTINCT or an outer GROUP BY/aggregation step is needed to collapse that repeated result down to one row per group.
Common Beginner Mistakes
Expecting MEDIAN() to exist, and behave as a plain aggregate, on every database
Only Oracle has a plain, GROUP BY-style MEDIAN() aggregate. MariaDB's MEDIAN() exists under the same name but needs OVER(), a window function, and MySQL and SQLite have no MEDIAN or percentile function at all.
Forgetting SQL Server's PERCENTILE_CONT needs OVER(), and writing it as a plain aggregate
That's a syntax error on SQL Server, PERCENTILE_CONT only exists there as an analytic (window) function, OVER() is required even to get a single value per group, unlike its plain-aggregate form on PostgreSQL and Oracle.
Best Practices
- Report median alongside, or instead of, average whenever a single extreme value could be skewing AVG()'s result, salary and latency data are classic examples.
- On SQL Server, wrap PERCENTILE_CONT's window-function call with DISTINCT (or an outer aggregation) to get one row per group rather than one per input row.
- On MySQL and SQLite, isolate the manual median logic (sorting, then picking the middle row by position) into a reusable view or query template, it's easy to get the even-count averaging step wrong.
Interview Questions
Why is median often preferred over average for something like salary data?
Average is pulled toward extreme outliers, a handful of very high (or very low) salaries can shift AVG() well away from what a typical employee actually earns. Median, the middle value once sorted, isn't affected the same way, a single outlier only shifts it if it's actually one of the middle values.
Which of these six databases has a plain, GROUP BY-style MEDIAN() aggregate, and how do the others get the same result?
Only Oracle, used exactly like any other aggregate function. PostgreSQL uses PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY col) as a plain aggregate too. SQL Server requires the same PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY col), but only as a window function with OVER(). MariaDB has its own MEDIAN(), also only as a window function. MySQL and SQLite have no built-in path at all, needing a manually sorted, position-based approach.
Summary
Median, the middle value of a sorted group, resists outliers in a way AVG() doesn't. Oracle alone has a real, plain-aggregate MEDIAN(). PostgreSQL gets there with a plain-aggregate PERCENTILE_CONT(0.5). SQL Server and MariaDB both have a working equivalent, but only as a window function requiring OVER(). MySQL and SQLite have no built-in path at all.
What's Next?
MODE, the single most frequently occurring value in a group, with its own native function on two databases and a manual GROUP BY/COUNT approach everywhere else.
Ready to Practice SQL?
Write and run real SQL queries online, right in your browser, no install or signup required. Pick a playground based on which database you want to practice on.
Both open a third-party SQL playground in a new tab. External site terms and privacy policy apply.
Or try 50 SQL practice questions →