AVG (Transact-SQL)
Returns the average of the values in a group. Null values are ignored. May be followed by the OVER clause.
.gif "Topic link icon")
Syntax
AVG ( [ ALL | DISTINCT ] expression )
Arguments
ALL
Applies the aggregate function to all values. ALL is the default.DISTINCT
Specifies that AVG be performed only on each unique instance of a value, regardless of how many times the value occurs.expression
Is an expression of the exact numeric or approximate numeric data type category, except for the bit data type. Aggregate functions and subqueries are not permitted.
Return Types
The return type is determined by the type of the evaluated result of expression.
Expression result |
Return type |
|---|---|
tinyint |
int |
smallint |
int |
int |
int |
bigint |
bigint |
decimal category (p, s) |
decimal(38, s) divided by decimal(10, 0) |
money and smallmoney category |
money |
float and real category |
float |
Remarks
If the data type of expression is an alias data type, the return type is also of the alias data type. However, if the base data type of the alias data type is promoted, for example from tinyint to int, the return value is of the promoted data type and not the alias data type.
AVG () computes the average of a set of values by dividing the sum of those values by the count of nonnull values. If the sum exceeds the maximum value for the data type of the return value an error will be returned.
Examples
A. Using the SUM and AVG functions for calculations
The following example calculates the average vacation hours and the sum of sick leave hours that the vice presidents of Adventure Works Cycles have used. Each of these aggregate functions produces a single summary value for all the retrieved rows.
USE AdventureWorks2008R2;
GO
SELECT AVG(VacationHours)AS 'Average vacation hours',
SUM (SickLeaveHours) AS 'Total sick leave hours'
FROM HumanResources.Employee
WHERE JobTitle LIKE 'Vice President%';
Here is the result set.
Average vacation hours Total sick leave hours
---------------------- ----------------------
25 97
(1 row(s) affected)
B. Using the SUM and AVG functions with a GROUP BY clause
When used with a GROUP BY clause, each aggregate function produces a single value for each group, instead of for the whole table. The following example produces summary values for each sales territory. The summary lists the average bonus received by the sales people in each territory and the sum of year-to-date sales for each territory.
USE AdventureWorks2008R2;
GO
SELECT TerritoryID, AVG(Bonus)as 'Average bonus', SUM(SalesYTD) as 'YTD sales'
FROM Sales.SalesPerson
GROUP BY TerritoryID;
GO
C. Using AVG with DISTINCT
The following statement returns the average list price of products.
USE AdventureWorks2008R2;
GO
SELECT AVG(DISTINCT ListPrice)
FROM Production.Product;
Here is the result set.
------------------------------
437.4042
(1 row(s) affected)
D. Using AVG without DISTINCT
Without DISTINCT, the AVG function finds the average list price of all products in the Product table.
USE AdventureWorks2008R2;
GO
SELECT AVG(ListPrice)
FROM Production.Product;
Here is the result set.
------------------------------
438.6662
(1 row(s) affected)