In SQL, the AVG() function is an aggregate function that returns the average of all values in a given expression.
It can also be used to return the average of all distinct (unique) values in an expression.
The expression must be numeric (it cannot be character string, bit string, or datetime).
Below are some basic examples to demonstrate how it works.
Sample Table
Suppose we have the following table:
SELECT * FROM Products;Result:
+-------------+------------+---------------------------------+----------------+-----------------------------------------+ | ProductId | VendorId | ProductName | ProductPrice | ProductDescription | |-------------+------------+---------------------------------+----------------+-----------------------------------------| | 1 | 1001 | Left handed screwdriver | 25.99 | Purple. Includes left handed carry box. | | 2 | 1001 | Long Weight (blue) | 14.75 | Includes a long wait. | | 3 | 1001 | Long Weight (green) | 11.99 | Approximate 30 minute waiting period. | | 4 | 1002 | Sledge Hammer | 33.49 | Wooden handle. Free wine glasses. | | 5 | 1003 | Chainsaw | 245.00 | Orange. Includes spare fingers. | | 6 | 1003 | Straw Dog Box | NULL | Tied with vines. Very chewable. | | 7 | 1004 | Bottomless Coffee Mugs (4 Pack) | 9.99 | Brown ceramic with solid handle. | | 8 | 1001 | Right handed screwdriver | 25.99 | Blue. Includes right handed carry box. | +-------------+------------+---------------------------------+----------------+-----------------------------------------+
Example
We can use the following query to get the average of all prices.
SELECT AVG(ProductPrice)
FROM Products;Result:
+--------------------+ | (No column name) | |--------------------| | 52.457142 | +--------------------+
In this case, price information is stored in the ProductPrice column, and so we pass that as an argument to the AVG() function, which then calculates the average and returns the result.
Using Column Aliases
You’ll notice that the previous results don’t include a column name. This is to be expected, because the AVG() function doesn’t return any columns. You can easily provide a column name by assigning an alias.
SELECT AVG(ProductPrice) AS Average
FROM Products;Result:
+-----------+ | Average | |-----------| | 52.457142 | +-----------+
Filtered Results
The AVG() function operates on the rows returned by the query. So if you filter the results, the result of AVG() will reflect that.
SELECT AVG(ProductPrice) AS Average
FROM Products
WHERE VendorId = 1001;Result:
+-----------+ | Average | |-----------| | 19.680000 | +-----------+
In this case, 19.680000 is the average price of all the products offered by the specified vendor.
NULL Values
The AVG() function ignores any NULL values. In our sample table above, product number 6 has got NULL in its ProductPrice column, but that was ignored in our AVG() example.
Depending on your DBMS and your settings, you may or may not see a warning that NULL values were eliminated in the result set.
Here’s an example of what you might see:
SELECT AVG(ProductPrice) AS Average
FROM Products;Result:
+-----------+ | Average | |-----------| | 52.457142 | +-----------+ Warning: Null value is eliminated by an aggregate or other SET operation.
All this tells us is that the column contained at least one NULL value, and that it was ignored when calculating the results.
Date/Time Data
The AVG() function does not accept date/time expressions.
Suppose we have the following table:
SELECT PetName, DOB
FROM Pets;Result:
+-----------+------------+ | PetName | DOB | |-----------+------------| | Fluffy | 2020-11-20 | | Fetch | 2019-08-16 | | Scratch | 2018-10-01 | | Wag | 2020-03-15 | | Tweet | 2020-11-28 | | Fluffy | 2020-09-17 | | Bark | NULL | | Meow | NULL | +-----------+------------+
If we try to use AVG() on the DOB column, we’ll get an error.
SELECT AVG(DOB) AS Average
FROM Pets;Result:
Msg 8117, Level 16, State 1, Line 1 Operand data type date is invalid for avg operator.
Character Data
The AVG() function also does not accept character string expressions.
Here’s what happens if we try to use AVG() on the ProductName column of our Products table (which uses a data type of varchar):
SELECT AVG(ProductName) AS Average
FROM Products;Result:
Msg 8117, Level 16, State 1, Line 1 Operand data type varchar is invalid for avg operator.
The DISTINCT Keyword
You can use the DISTINCT keyword with AVG() to calculate only distinct values. That is, if there are any duplicate values, they are treated as one value.
Example:
SELECT AVG(DISTINCT ProductPrice) AS DistinctAverage
FROM Products;Result:
+-------------------+ | DistinctAverage | |-------------------| | 56.868333 | +-------------------+
We can see that this result is higher than the result we got without the DISTINCT keyword.
To recap, we got 52.457142 without the DISTINCT keyword, and 56.868333 with the DISTINCT keyword.
This is because there are two items that share the same price (the left handed screwdriver and right handed screwdriver are both priced at 25.99). Therefore, the AVG() function, when used with the DISTINCT keyword, treats both of those values as one, and calculates its result accordingly.
Window Functions
Depending on your DBMS, you may be able to use an OVER clause with your AVG() function to create a window function.
A window function performs an aggregate-like operation on a set of query rows. It produces a result for each query row. This is in contrast to an aggregate operation, which groups query rows into a single result row.
Here’s an example to demonstrate the concept.
We’ve already seen the Products table. Our database also has a Customers table, and it contains the following data:
+--------------+----------------------+-------------------+------------+-----------------+------------+-----------+----------------+ | CustomerId | CustomerName | PostalAddress | City | StateProvince | ZipCode | Country | Phone | |--------------+----------------------+-------------------+------------+-----------------+------------+-----------+----------------+ | 1001 | Palm Pantry | 20 Esplanade | Townsville | QLD | 2040 | AUS | (308) 555-0100 | | 1002 | Tall Poppy | 12 Main Road | Columbus | OH | 43333 | USA | (310) 657-0134 | | 1003 | Crazy Critters | 10 Infinite Loops | Cairns | QLD | 4870 | AUS | (418) 555-0143 | | 1004 | Oops Media | 4 Beachside Drive | Perth | WA | 1234 | AUS | (405) 443-5987 | | 1005 | Strange Names Inc. | 789 George Street | Sydney | NSW | 2000 | AUD | (318) 777-0177 | | 1006 | Hi-Five Solutionists | 5 High Street | Highlands | HI | 1254 | AUS | (415) 413-5182 | +--------------+----------------------+-------------------+------------+-----------------+------------+-----------+----------------+
We can retrieve data from these tables and present them as one result set by using a join.
We can also use the AVG() function with the OVER clause to apply a window function to the data.
SELECT
v.VendorName,
p.ProductName,
p.ProductPrice,
AVG(ProductPrice) OVER (PARTITION BY v.VendorName) AS "Average For This Vendor"
FROM Products p
INNER JOIN Vendors v
ON v.VendorId = p.VendorId
ORDER BY VendorName, ProductPrice, "Average For This Vendor";Result:
+---------------+---------------------------------+----------------+---------------------------+ | VendorName | ProductName | ProductPrice | Average For This Vendor | |---------------+---------------------------------+----------------+---------------------------| | Katty Kittens | Bottomless Coffee Mugs (4 Pack) | 9.99 | 9.990000 | | Mars Supplies | Long Weight (green) | 11.99 | 19.680000 | | Mars Supplies | Long Weight (blue) | 14.75 | 19.680000 | | Mars Supplies | Right handed screwdriver | 25.99 | 19.680000 | | Mars Supplies | Left handed screwdriver | 25.99 | 19.680000 | | Pedal Medals | Straw Dog Box | NULL | 245.000000 | | Pedal Medals | Chainsaw | 245.00 | 245.000000 | | Randy Roofers | Sledge Hammer | 33.49 | 33.490000 | +---------------+---------------------------------+----------------+---------------------------+
In this case we used the OVER clause with our AVG() function to partition the result by vendor name.
By doing this, we were able to return price information for each product, as well as the average price of all products from that given vendor. The average price changes as the vendor changes (unless multiple vendors happen to have the same average), but remains the same for all products from the same vendor.
This concept can also be applied to other aggregate functions in SQL, such as SUM(), MIN(), MAX(), and COUNT().