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.

 

Syntax

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.

 

Parameters

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.

Result:

MEDIAN_FEES_REQ
300

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

Result:

FIRST_NAMELAST_NAMEADDRESS_STATEFEES_REQUIREDMEDIAN_FEES_REQ
MarkAndersonCalifornia860860
RobertPickeringColorado110130
SusanJohnsonColorado150130
MichelleRandallFlorida250250
JohnRogersNevada210280
TomCapperNevada350280
StevenWebberNew York100500
MarkHollowayNew York500500
JohnSmithNew York500500
MaryTaylorOregon500500
JulieArmstrongTexas100150
TanyaHallTexas150150
AndrewCooperTexas800150
JarradWinstonUtah700700

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 21-day Oracle Tips email course.

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