How to Calculate the Mean, Median, and Mode in OracleCalculating 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?

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

 

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.

Want an Oracle SQL function cheat sheet for easy reference? Click here to get a PDF file that contains all of Oracle functions for you to reference or print out.

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

How Can I Calculate the Median in Oracle SQL?

To calculate the mean in Oracle SQL,

Let’s calculate the mean

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_RATINGMEDIAN_RATINGMODE_RATING
14.751412

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.

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 an Oracle SQL function cheat sheet for easy reference? Click here to get a PDF file that contains all of Oracle functions for you to reference or print out.

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

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