• Article

AVG (Transact-SQL)

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

Topic link iconTransact-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

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)