SQL AVG Function with Examples

The SQL AVG function allows you to find the average value from the specified values. Learn how to use it and see some examples in this article.

What is the SQL AVG Function

The SQL 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.

 

SQL AVG 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] expression )

The syntax of AVG as an analytic function is:

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

 

Parameters

The parameters of the AVG function are:

• expression (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 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.

 

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_ID	FIRST_NAME	LAST_NAME	FEES_REQUIRED	FEES_PAID	ENROLMENT_DATE	GENDER
1	John	Smith	500	100	01-Feb-21	M
2	Susan	Johnson	150	150	12-Jan-21	F
3	Tom	Capper	350	320	06-Mar-21	M
4	Mark	Holloway	500	410	20-Jan-21	M
5	Steven	Webber	100	80	09-Mar-21	M
6	Julie	Armstrong	100	0	12-Feb-21	F
7	Michelle	Randall	250		23-Jan-21	F
8	Andrew	Cooper	800	400	04-Mar-21	M
9	Robert	Pickering	110	100	30-Jan-21	M
10	Tanya	Hall	150	150	28-Jan-21	F

 

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-21

 

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:

GENDER	AVG_FEES_PAID
M	235
F	100

 

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:

GENDER	AVG_FEES_PAID
M	235

 

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_MONTH	AVG_FEES_PAID
MAR	266.6666667
FEB	50
JAN	202.5

 

Can I Use AVG for a Date?

Yes, you can.

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 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 “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 for more information.

 

Similar Functions

Some functions which are similar to the AVG 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. Other function lists are coming soon.