# AVG (Transact-SQL)

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

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.

### 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)