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
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
DISTINCT modifier before the expression.
ALLmodifier means the
AVGfunction is applied to all values.
ALLmodifier is used by default if you do not specify any modifier explicitly.
DISTINCTmodifier means the
AVGfunction is applied to only unique values in the set.
It is important to note that the
AVG function ignores
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
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.