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?
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:
How could we find the mean of these values?
We can use the AVG function.
SELECT AVG(rating) AS mean_rating
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,
Let’s calculate the mean
SELECT MEDIAN(rating) AS median_rating
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
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
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 To Improve Your Oracle SQL?
Improve your Oracle SQL queries by signing up to my free 21-day Oracle Tips email course.