Oracle AVG FunctionThe Oracle AVG function is a common function in Oracle SQL. I’ll explain how to use it in this article and show you some examples.

Purpose of the Oracle AVG Function

The AVG function calculates the average of a series of values that you provide to it.

Most of the time, this will be a particular column that you specify, so the average will be all of the values in that column.

Just like the MIN and MAX functions, the AVG function is a SQL standard column. It works in many different versions of SQL. In this article, I’ll be looking at the Oracle version.

 

Syntax

There are two ways you can run the AVG function – as an aggregate function, or an analytic function.

The syntax of AVG as an aggregate function is:

AVG ( [DISTINCT/ALL] expr )

The syntax of AVG as an analytic function is:

AVG ( [DISTINCT/ALL] expr ) OVER ( analytic_clause )

 

Parameters

The parameters of the AVG function are:

  • expr (mandatory): This is the expression that is being used to calculate an average. It can be a set of values, a column name, or any other valid Oracle expression.
  • analytic_clause (optional): This is used when you want to use AVG as an analytic function. It works similar to grouping data with the AVG function, but it’s a bit different. Refer to the examples below for more information.

 

Can I Use Oracle AVG for a Date?

You can use the AVG function to calculate an average date, if that’s what you need for your situation.

To do this, you can

  1. Convert your dates to a Julian number
  2. Average those numbers
  3. Round them off to a whole number
  4. Convert it back to a date

The query to do this would be:

SELECT TO_DATE(ROUND(AVG(TO_NUMBER(TO_CHAR(your_date_field, 'J')))), 'J')
FROM your_column;

See the examples for the output of this. Thanks to user “Dan A” from StackOverflow for this solution.

For more information on these functions, read my guides on the TO_DATE, TO_NUMBER, and TO_CHAR functions.

 

How Does the Oracle AVG Function and NULLs Work?

The AVG function, and all aggregate functions, ignore any NULL values that it finds.

This is because NULL values are unknown, and not equal to 0.

So, if you have a range of values such as:

5, 6, 2, 3, 4, NULL

If you were to perform an AVG on this, you will get 4. (5+6+2+3+4 = 20/5 = 4).

The NULL value is not included.

If you wanted to include it, or treat it as a 0, you can use the COALESCE function to convert it to 0.

SELECT AVG(COALESCE(your_column, 0)) FROM your_table

You would then get an answer of 3.33 (5+6+2+3+4+0 = 20/6 = 3.33).

 

What Does The “Oracle AVG Over” Do?

Using the OVER keyword on the AVG function lets you write it as an analytic function. This means you can use it in a different way.

This OVER clause allows you to specify what you want the AVG function to be grouped by, or what it is contained to.

Normally, the AVG function gets the average of everything you supply it. You can use the OVER clause to specify that you want the average of a certain group of fields (e.g. the AVG salary of employees in each region).

Have a look at the examples below for more information.

 

Examples of the AVG Function

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

Sample Table

This is the table that I’ll be using for these queries.

SELECT * FROM student

 

STUDENT_IDFIRST_NAMELAST_NAMEFEES_REQUIREDFEES_PAIDENROLMENT_DATEGENDER
1JohnSmith50010001-Feb-15M
2SusanJohnson15015012-Jan-15F
3TomCapper35032006-Mar-15M
4MarkHolloway50041020-Jan-15M
5StevenWebber1008009-Mar-15M
6JulieArmstrong100012-Feb-15F
7MichelleRandall25023-Jan-15F
8AndrewCooper80040004-Mar-15M
9RobertPickering11010030-Jan-15M
10TanyaHall15015028-Jan-15F

 

Example 1 – Simple Example

This is a simple example using a number field.

SELECT AVG(fees_paid) AS avg_fees_paid
FROM student;

Result:

AVG_FEES_PAID
190

 

Example 2 – AVG Date

This example uses a few functions to find an average of a date field.

SELECT TO_DATE(ROUND(AVG(TO_NUMBER(TO_CHAR(enrolment_date, 'J')))), 'J') AS avg_date
FROM student;

Result:

AVG_DATE
07-Feb-15

 

Example 3 – GROUP BY

This example uses a GROUP BY clause to group the averages by the gender field.

SELECT gender, AVG(fees_paid) AS avg_fees_paid
FROM student
GROUP BY gender;

Result:

GENDERAVG_FEES_PAID
M235
F100

 

Example 4 – HAVING

This example uses the HAVING clause to restrict the rows returned after the GROUP BY has been applied.

SELECT gender, AVG(fees_paid) AS avg_fees_paid
FROM student
GROUP BY gender
HAVING AVG(fees_paid) > 200;

Result:

GENDERAVG_FEES_PAID
M235

 

Example 5 – Analytic Function

This example uses AVG as an analytic function. It finds the average fees per month.

SELECT TO_CHAR(enrolment_date, 'MON') AS ENROLMENT_MONTH,
AVG(fees_paid) AS avg_fees_paid
FROM student
GROUP BY TO_CHAR(enrolment_date, 'MON');

Result:

ENROLMENT_MONTHAVG_FEES_PAID
MAR266.6666667
FEB50
JAN202.5

 

Similar Functions

Some functions which are similar to the XXX function are:

  • MAX – This function gets the highest of the supplied values. It’s the opposite of the MIN function.
  • MIN – This function gets the lowest of the supplied values. It’s the opposite of the MAX function.
  • SUM – This function adds up all of the values supplied to it.
  • COUNT – This function counts the number of values supplied.

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!

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