Calculating the mean, median, and mode in Oracle SQL is a task often performed by report developers or anyone else working with SQL. Learn how to calculate these three values from a range of data in this article.

## What is the Mean, Median, and Mode, and Standard Deviation?

Mean, median, and mode are three kinds of average values that can be obtained from a set of data.

You might have learnt about them in high school maths (like I did) but may have forgotten what they are (like I did).

**Mean**: This is the “average” that you might know. It’s the sum of all of the numbers divided by the count of numbers.**Median**: This is the middle value of a list of numbers.**Mode**: This is the value that occurs most often**Standard Deviation**: A value that represents how much numbers differ from each other

Get Your Free PDF: 9 Ways to Improve your Database Skills

## How Can I Calculate the Mean in Oracle SQL?

Let’s say we had a table of data called movie_ratings that looked like this:

RATING |

11 |

12 |

15 |

19 |

12 |

13 |

16 |

20 |

How could we find the mean of these values?

We can use the AVG function.

`SELECT AVG(rating) AS mean_rating`

FROM movie_ratings;

Result:

MEAN_RATING |

14.75 |

You can see the average is 14.75. This is because we added all of the numbers up (which equals 118) and then divided by the count of numbers (which is 8). 118/8 = 14.75.

## How Can I Calculate the Median in Oracle SQL?

To calculate the mean in Oracle SQL, we use the 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.

### Median Function Parameters

The MEDIAN function 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.

### Finding the Median

The MEDIAN function looks like this:

`SELECT MEDIAN(rating) AS median_rating`

FROM movie_ratings;

Result:

MEDIAN_RATING |

14 |

You can see that the median is 14. This is because the middle value would be 14.

While there are 8 values and there is no middle value, the median is calculated as the point between the two middle values.

So, if we order the values they would be:

11, 12, 12, 13, 15, 16, 19, 20

The middle two values are 13 and 15, and the mid point between them is 14.

## How Can I Calculate the Mode in Oracle SQL?

To calculate the mode, we need to use the STATS_MODE function.

`SELECT STATS_MODE(rating) AS mode_rating`

FROM movie_ratings;

Result:

MODE_RATING |

12 |

The mode is 12 as that is the most occurring value in the table.

## Show the Mean, Median and Mode All In One Query

Let’s use these examples to show the mean, median, and mode all in the same query.

`SELECT`

AVG(rating) AS mean_rating,

MEDIAN(rating) AS median_rating,

STATS_MODE(rating) AS mode_rating

`FROM movie_ratings;`

Result:

MEAN_RATING |
MEDIAN_RATING |
MODE_RATING |

14.75 | 14 | 12 |

As you can see, the values here are the same as the earlier examples.

So, that’s how you find the mean, median, and mode in Oracle SQL.

## Examples of the MEDIAN Function

Here are some further 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.

## How to Find the Standard Deviation in Oracle

Another statistic you may want to find is the standard deviation.

This can be achieved with the STDDEV function.

The purpose of the STDDEV function is to find the standard deviation of a set of numbers.

The standard deviation is a mathematical concept, which is a number that represents how much the values in a group differ from the mean value in a group.

This function can be used as either an analytical function or an aggregate function.

## Oracle STDDEV Function Syntax and Parameters

The syntax for the STDDEV function when it is used as an aggregate function is:

STDDEV ( [DISTINCT | ALL] expression )

Alternatively, the syntax when used as an analytical function is:

STDDEV ( [DISTINCT | ALL] expression ) [OVER (analytical_clause) ]

The parameters of the STDDEV function as an aggregate function are:

- DISTINCT|ALL (optional): Using DISTINCT means that the function will only look at unique values. Using ALL means that all values will be considered.
- expression (mandatory): The expression is the set of data or the column that is used in the STDDEV function.

The parameters of the STDDEV function as an analytical function are the same, but also include:

- analytical_clause (optional): This is the clause or logic that is used to group the values for the STDDEV function.

## Examples of the STDDEV Function

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

### Example 1

This example uses the STDDEV funiotn as an aggregate function.

`SELECT STDDEV(fees_required)`

FROM student;

Result:

STDDEV(FEES_REQUIRED) |

234.7788558 |

### Example 2

This example uses the STDDEV function as an aggregate function again, but I’ve used the DISTINCT keyword.

`SELECT STDDEV(DISTINCT fees_required)`

FROM student;

Result:

STDDEV(DISTINCTFEES_REQUIRED) |

254.9322973 |

### Example 3

This example uses the STDDEV function as an analytical function.

`SELECT`

enrolment_date,

TO_CHAR(enrolment_date, ‘MON’) AS enrolment_mth,

STDDEV(fees_required) OVER (order by TO_CHAR(enrolment_date, ‘MON’)) AS stdev_val

FROM student;

Result:

ENROLMENT_DATE |
ENROLMENT_MTH |
STDEV_VAL |

01/Feb/15 | FEB | 282.8427125 |

12/Feb/15 | FEB | 282.8427125 |

30/Jan/15 | JAN | 176.5812911 |

12/Jan/15 | JAN | 176.5812911 |

20/Jan/15 | JAN | 176.5812911 |

28/Jan/15 | JAN | 176.5812911 |

23/Jan/15 | JAN | 176.5812911 |

04/Mar/15 | MAR | 234.7788558 |

06/Mar/15 | MAR | 234.7788558 |

09/Mar/15 | MAR | 234.7788558 |

You can see that for each of the enrolment_mth values that are the same, the stdev_val is the same.

So, that’s how you find the mean, median, mode, and standard deviation in Oracle SQL.

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!

Get Your Free PDF: 9 Ways to Improve your Database Skills

Thanks for the article.

Seems the result table of the analytic MEDIAN example has wrong median values for Colorado:

Robert Pickering Colorado 110 130

Susan Johnson Colorado 150 130

The 130 are the average, but the MEDIAN should be 150. Confused me as it is the first state with >1 students in the list :-)

Hi Rolf, yes that does seem strange. I re-ran the code to see what the value would be, and the median still shows as 130. A quick test can be run:

CREATE TABLE testval (

testnum NUMBER(4)

);

INSERT INTO testval(testnum) VALUES (110);

INSERT INTO testval(testnum) VALUES (150);

SELECT MEDIAN(testnum) FROM testval;

It seems Oracle chooses a value that would be the middle point if there is an even number of values. New York shows 500 because the values are 100,500,500 and the middle value is 500. The same thing happens for Texas which shows 100,150,800 and has a median of 150.

Hope that makes sense!