AVG (Transact-SQL)

Returns the average of the values in a group. Null values are ignored. May be followed by the OVER clause.

Transact-SQL Syntax Conventions

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
integer category	int
decimal category (p, s)	decimal(38, s) divided by decimal(10, 0)
money and smallmoney category	money
float and real category	float

Important

Distinct aggregates, for example, AVG(DISTINCT column_name), COUNT(DISTINCT column_name), MAX(DISTINCT column_name), MIN(DISTINCT column_name), and SUM(DISTINCT column_name), are not supported when you use CUBE or ROLLUP. If these are used, the SQL Server 2005 Database Engine returns an error message and cancels the query.

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.

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 AdventureWorks;
GO
SELECT AVG(VacationHours)as 'Average vacation hours', 
    SUM  (SickLeaveHours) as 'Total sick leave hours'
FROM HumanResources.Employee
WHERE Title 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 AdventureWorks;
GO
SELECT TerritoryID, AVG(Bonus)as 'Average bonus', SUM(SalesYTD) '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 AdventureWorks;
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 AdventureWorks;
GO
SELECT AVG(ListPrice)
FROM Production.Product;

Here is the result set.

------------------------------
438.6662
(1 row(s) affected)

See Also

Reference

Aggregate Functions (Transact-SQL)
OVER Clause (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance