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.

SELECT MEDIAN(fees_required) AS median_fees_req
FROM student;

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

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

Result:

FIRST_NAME LAST_NAME ADDRESS_STATE FEES_REQUIRED MEDIAN_FEES_REQ
Mark Anderson California 860 860
Robert Pickering Colorado 110 130
Susan Johnson Colorado 150 130
Michelle Randall Florida 250 250
John Rogers Nevada 210 280
Tom Capper Nevada 350 280
Steven Webber New York 100 500
Mark Holloway New York 500 500
John Smith New York 500 500
Mary Taylor Oregon 500 500
Julie Armstrong Texas 100 150
Tanya Hall Texas 150 150
Andrew Cooper Texas 800 150
Jarrad Winston Utah 700 700

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!

Improve Your Oracle SQL With My 10-Day Email Course

x