Oracle MEDIAN Function with ExamplesIn this article, I’ll explain what the Oracle MEDIAN function is, and show you some examples.

Purpose of the Oracle MEDIAN Function

The MEDIAN function returns the median of the set of provided values.

The MEDIAN is the middle value in a set of values.

So, for example, you had these five values:

10, 15, 18, 23, 40.

The median of these would be the middle value after they have been ordered in ascending order. So, the median would be 18.

I’ve written an article on finding the median, mean, and mode in Oracle if you’re looking for different ways of finding a mid-point.



The syntax of the MEDIAN function is:

MEDIAN ( expr ) OVER (query_partition_clause).

It can be used as either an analytic function or an aggregate function.



The parameters of the MEDIAN function are:

  • expr (mandatory): The expression to calculate a median for. This can be a set of numbers, or a column.
  • query_partition_clause (optional): The clause that is used to partition the data when using MEDIAN as an analytic query.

The expr value can be any numeric data type. The MEDIAN function returns the same data type as the expr value.

If you specify the OVER clause, Oracle will work out the data type with the highest precedence and return that type.


Examples of the MEDIAN Function

Here are some examples of the MEDIAN function. I find that examples are the best way for me to learn about code, even with the explanation above.

Example 1 – aggregate

This example finds the median using it as an aggregate function.

SELECT MEDIAN(fees_required) AS median_fees_req
FROM student;



It shows the value of 300 because this is the middle value in the column after it has been ordered.


Example 2 – analytic

This example uses the MEDIAN function as an analytic function

SELECT first_name, last_name, address_state, fees_required, 
MEDIAN(fees_required) OVER (PARTITION BY address_state) AS median_fees_req
FROM student;


StevenWebberNew York100500
MarkHollowayNew York500500
JohnSmithNew York500500

This example shows the median fees_required when grouped by the home_state for each record in the table.

Similar Functions

Some functions which are similar to the MEDIAN function are:

  • AVG: Returns the average of the set of values.
  • PERCENTILE_CONT: This function finds the value for a specified percentile. If you specify the percentile as 0.5, it should return the median.

If you want to know more about SQL functions, you can find a full list of Oracle SQL functions here.

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!

Want To Improve Your Oracle SQL?

Improve your Oracle SQL queries by signing up to my free 10-day Oracle Tips email course.

BONUS: Oracle SQL functions PDF cheat sheet

We won't send you spam. Unsubscribe at any time. Powered by ConvertKit