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
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
DISTINCT modifier before the expression.
ALLmodifier means that the
AVGfunction is applied to all values including duplicates. The
AVG()function uses the
ALLmodifier by default if you do not specify any modifier explicitly.
DISTINCTmodifier means that the
AVGfunction is applied to only distinct values in the set of values.
Notice that the
AVG function ignores
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.
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
SELECT AVG(unitprice) FROM products;
The query calculates the total unit prices and divides the total by the number of rows in the
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 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;
The INNER JOIN clause is used to get the category name from the
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;
In this tutorial, we have shown you how to use the SQL
AVG function to calculate the average of a list of values.