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.
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 )
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
- Convert your dates to a Julian number
- Average those numbers
- Round them off to a whole number
- 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.
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.
This is the table that I’ll be using for these queries.
SELECT * FROM student
Example 1 – Simple Example
This is a simple example using a number field.
SELECT AVG(fees_paid) AS avg_fees_paid FROM student;
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;
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;
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;
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');
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!