Select Page

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:

 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.

## 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_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.

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.

x