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)

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.

products table

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;
SQL AVG example

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;

SQL AVG DISTINCT example

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;
SQL AVG GROUP BY example

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;
SQL AVG HAVING example

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