SQL AVG Function

Summary: in this tutorial, you will learn how to use the SQL AVG aggregate function to calculate the average of a set of numbers.

Introduction to SQL AVG function

The AVG function calculates the average of the values. To use the AVG function, you use the following syntax:

AVG (ALL | DISTINCT expression)Code language: SQL (Structured Query Language) (sql)

You can specify ALL or DISTINCT modifier before the expression.

• ALL modifier means that the AVG function is applied to all values including duplicates. The  AVG() function uses the ALL modifier by default if you do not specify any modifier explicitly.
• DISTINCT modifier means that the AVG function is applied to only distinct values in the set of values.

Notice that the AVG function ignores NULL values.

The AVG function returns a single value whose data type is determined by the type of the result of the expression. The returned data type could be any numeric type such as integer, float, etc.

Behind the scenes, the AVG function calculates the average of values by dividing the total of these values by the number of values except for the NULL values. Therefore, if the total of those values exceeds the maximum value of data type of the result, the database server will issue an error.

The AVG function is one of an ANSI SQL aggregate functions, therefore, it is available in all relational database management systems e.g., Oracle, Microsoft SQL Server, MySQL, PostgreSQL, etc.

SQL AVG function examples

We will use the products table in the sample database for the demonstration of the  AVG() function in the following sections.

Simple SQL AVG function example

The following query calculates the average of unit prices of all products in the products table.

SELECT 
    AVG(unitprice)
FROM
    products;Code language: SQL (Structured Query Language) (sql)

The query calculates the total unit prices and divides the total by the number of rows in the products table.

To calculate the average of distinct unit prices of products, you can use the DISTINCT modifier in the  AVG() function as the following query:

SELECT 
    AVG(DISTINCT unitprice)
FROM
    products;Code language: SQL (Structured Query Language) (sql)

SQL AVG function with GROUP BY clause

To find the average of unit prices for each product’s category, you can use the AVG function with the GROUP BY clause as the following query:

SELECT 
    categoryname, AVG(unitprice)
FROM
    products
INNER JOIN
    categories ON categories.categoryid = products.categoryid
GROUP BY categoryname;Code language: SQL (Structured Query Language) (sql)

The INNER JOIN clause is used to get the category name from the categories table.

SQL AVG function with HAVING clause

To get the category that has an average unit price greater than $25, you use the AVG function with  GROUP BY and HAVING clauses as the following query:

SELECT 
    categoryname, AVG(unitprice)
FROM
    products
INNER JOIN
    categories ON categories.categoryid = products.categoryid
GROUP BY categoryname
HAVING AVG(unitprice) > 25;Code language: SQL (Structured Query Language) (sql)

In this tutorial, we have shown you how to use the SQL AVG function to calculate the average of a list of values.