AVG (Transact-SQL)

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) SQL analytics endpoint in Microsoft Fabric Warehouse in Microsoft Fabric

This function returns the average of the values in a group. It ignores null values.

Transact-SQL syntax conventions

Syntax

AVG ( [ ALL | DISTINCT ] expression )
   [ OVER ( [ partition_by_clause ] order_by_clause ) ]

Note

To view Transact-SQL syntax for SQL Server 2014 (12.x) and earlier versions, see Previous versions documentation.

Arguments

ALL

Applies the aggregate function to all values. ALL is the default.

DISTINCT

Specifies that AVG operates only on one unique instance of each value, regardless of how many times that value occurs.

expression

An expression of the exact numeric or approximate numeric data type category, except for the bit data type. Aggregate functions and subqueries aren't permitted.

OVER ( [ partition_by_clause ] order_by_clause)

partition_by_clause divides the result set produced by the FROM clause into partitions to which the function is applied. If not specified, the function treats all rows of the query result set as a single group. The order_by_clause determines the logical order in which the operation is performed. The order_by_clause is required. For more information, see OVER Clause (Transact-SQL).

Return types

The evaluated result of expression determines the return type.

Expression result	Return type
tinyint	int
smallint	int
int	int
bigint	bigint
decimal category (p, s)	decimal(38, max(s,6))
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 takes 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 non-null values. If the sum exceeds the maximum value for the data type of the return value, AVG() returns an error.

AVG is a deterministic function when used without the OVER and ORDER BY clauses. It's nondeterministic when specified with the OVER and ORDER BY clauses. For more information, see Deterministic and Nondeterministic Functions. Also, AVG may appear to behave as a nondeterministic function when you use it with float and real data types. But the underlying reason is the approximate nature of these data types.

Examples

A. Using the SUM and AVG functions for calculations

This 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. The example uses the AdventureWorks2022 database.

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 covering each group, instead of a single value covering the whole table. The following example produces summary values for each sales territory in the AdventureWorks2022 database. 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.

SELECT TerritoryID, AVG(Bonus)as 'Average bonus', SUM(SalesYTD) as 'YTD sales'
FROM Sales.SalesPerson
GROUP BY TerritoryID;
GO

Here is the result set.

TerritoryID Average Bonus         YTD Sales
----------- --------------------- ---------------------
NULL        0.00                  1252127.9471
1           4133.3333             4502152.2674
2           4100.00               3763178.1787
3           2500.00               3189418.3662
4           2775.00               6709904.1666
5           6700.00               2315185.611
6           2750.00               4058260.1825
7           985.00                3121616.3202
8           75.00                 1827066.7118
9           5650.00               1421810.9242
10          5150.00               4116871.2277

(11 row(s) affected)

C. Using AVG with DISTINCT

This statement returns the average list price of products in the AdventureWorks2022 database. By using DISTINCT, the calculation considers only unique values.

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 in the AdventureWorks2022 database, including any duplicate values.

SELECT AVG(ListPrice)
FROM Production.Product;

Here is the result set.

------------------------------
438.6662

(1 row(s) affected)

E. Using the OVER clause

The following example uses the AVG function with the OVER clause, to provide a moving average of yearly sales for each territory in the Sales.SalesPerson table in the AdventureWorks2022 database. The data is partitioned by TerritoryID and logically ordered by SalesYTD. This means that the AVG function is computed for each territory based on the sales year. For TerritoryID 1, there are two rows for sales year 2005, which represent the two sales-people with sales that year. The average sales for these two rows is calculated, and then the third row representing sales for the year 2006 is included in the calculation.

SELECT BusinessEntityID, TerritoryID
   ,DATEPART(yy,ModifiedDate) AS SalesYear
   ,CONVERT(VARCHAR(20),SalesYTD,1) AS  SalesYTD
   ,CONVERT(VARCHAR(20),AVG(SalesYTD) OVER (PARTITION BY TerritoryID
                                            ORDER BY DATEPART(yy,ModifiedDate)
                                           ),1) AS MovingAvg
   ,CONVERT(VARCHAR(20),SUM(SalesYTD) OVER (PARTITION BY TerritoryID
                                            ORDER BY DATEPART(yy,ModifiedDate)
                                            ),1) AS CumulativeTotal
FROM Sales.SalesPerson
WHERE TerritoryID IS NULL OR TerritoryID < 5
ORDER BY TerritoryID,SalesYear;

Here is the result set.

BusinessEntityID TerritoryID SalesYear   SalesYTD             MovingAvg            CumulativeTotal
---------------- ----------- ----------- -------------------- -------------------- --------------------
274              NULL        2005        559,697.56           559,697.56           559,697.56
287              NULL        2006        519,905.93           539,801.75           1,079,603.50
285              NULL        2007        172,524.45           417,375.98           1,252,127.95
283              1           2005        1,573,012.94         1,462,795.04         2,925,590.07
280              1           2005        1,352,577.13         1,462,795.04         2,925,590.07
284              1           2006        1,576,562.20         1,500,717.42         4,502,152.27
275              2           2005        3,763,178.18         3,763,178.18         3,763,178.18
277              3           2005        3,189,418.37         3,189,418.37         3,189,418.37
276              4           2005        4,251,368.55         3,354,952.08         6,709,904.17
281              4           2005        2,458,535.62         3,354,952.08         6,709,904.17

(10 row(s) affected)

In this example, the OVER clause doesn't include PARTITION BY. This means that the function applies to all rows returned by the query. The ORDER BY clause specified in the OVER clause determines the logical order to which the AVG function applies. The query returns a moving average of sales, by year, for all sales territories specified in the WHERE clause. The ORDER BY clause specified in the SELECT statement determines the order in which the SELECT statement displays the rows of the query.

SELECT BusinessEntityID, TerritoryID
   ,DATEPART(yy,ModifiedDate) AS SalesYear
   ,CONVERT(VARCHAR(20),SalesYTD,1) AS  SalesYTD
   ,CONVERT(VARCHAR(20),AVG(SalesYTD) OVER (ORDER BY DATEPART(yy,ModifiedDate)
                                            ),1) AS MovingAvg
   ,CONVERT(VARCHAR(20),SUM(SalesYTD) OVER (ORDER BY DATEPART(yy,ModifiedDate)
                                            ),1) AS CumulativeTotal
FROM Sales.SalesPerson
WHERE TerritoryID IS NULL OR TerritoryID < 5
ORDER BY SalesYear;

Here is the result set.

BusinessEntityID TerritoryID SalesYear   SalesYTD             MovingAvg            CumulativeTotal
---------------- ----------- ----------- -------------------- -------------------- --------------------
274              NULL        2005        559,697.56           2,449,684.05         17,147,788.35
275              2           2005        3,763,178.18         2,449,684.05         17,147,788.35
276              4           2005        4,251,368.55         2,449,684.05         17,147,788.35
277              3           2005        3,189,418.37         2,449,684.05         17,147,788.35
280              1           2005        1,352,577.13         2,449,684.05         17,147,788.35
281              4           2005        2,458,535.62         2,449,684.05         17,147,788.35
283              1           2005        1,573,012.94         2,449,684.05         17,147,788.35
284              1           2006        1,576,562.20         2,138,250.72         19,244,256.47
287              NULL        2006        519,905.93           2,138,250.72         19,244,256.47
285              NULL        2007        172,524.45           1,941,678.09         19,416,780.93
(10 row(s) affected)

Related content

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