Using SQL AVG Function

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

Introduction to SQL AVG

The AVG is one of ANSI SQL aggregate functions therefore it is available in all relational database management systems. The AVG function calculates the average of the set of numbers and returns a single value. To call 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 the AVG function is applied to all values. ALL modifier is used by default if you do not specify any modifier explicitly.
• DISTINCT modifier means the AVG function is applied to only unique values in the set.

It is important to note that the AVG function ignores NULL values.

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

Behind the scene, the AVG function calculates average of the set 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 numbers exceeds the maximum value of data type of the result, the database server will issue an error.

Let’s practice with several examples to see what we can achieve by using the AVG function.

SQL AVG function examples

Simple SQL AVG function example

The following query calculates average unit price of all products:

SELECT AVG( unitprice ) "Average Unit Price"
FROM products

The query calculates total of unit prices and divides the total by the number of rows. To find the average of unique prices, you can use the DISTINCT modifier as follows:

SELECT AVG( DISTINCT unitprice ) "Average Unit Price"
FROM products

SQL AVG function with a GROUP BY clause

To find the average unit price for each product’s category, you can use 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

In this tutorial, we have shown you how to use SQL AVG function to calculate average of set of values with several examples.