FB

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.

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Table of Contents