--- title: "AVG (Transact-SQL) | Microsoft Docs" ms.custom: "" ms.date: "07/24/2017" ms.prod: "sql-non-specified" ms.reviewer: "" ms.suite: "" ms.technology: - "database-engine" ms.tgt_pltfrm: "" ms.topic: "language-reference" f1_keywords: - "AVG_TSQL" - "AVG" dev_langs: - "TSQL" helpviewer_keywords: - "AVG function [Transact-SQL]" - "GROUP BY clause, AVG function" - "DISTINCT keyword" - "values [SQL Server], average" - "average values" ms.assetid: 4534b705-d946-441b-9b5d-5fbe561c9131 caps.latest.revision: 52 author: "BYHAM" ms.author: "rickbyh" manager: "jhubbard" --- # AVG (Transact-SQL) [!INCLUDE[tsql-appliesto-ss2008-all_md](../../includes/tsql-appliesto-ss2008-all-md.md)] Returns the average of the values in a group. Null values are ignored. ![Topic link icon](../../database-engine/configure-windows/media/topic-link.gif "Topic link icon") [Transact-SQL Syntax Conventions](../../t-sql/language-elements/transact-sql-syntax-conventions-transact-sql.md) ## Syntax ```sql AVG ( [ ALL | DISTINCT ] expression ) OVER ( [ partition_by_clause ] order_by_clause ) ``` ## 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](../../t-sql/language-elements/expressions-transact-sql.md) of the exact numeric or approximate numeric data type category, except for the **bit** data type. Aggregate functions and subqueries are not 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. *order_by_clause* determines the logical order in which the operation is performed. *order_by_clause* is required. For more information, see [OVER Clause (Transact-SQL)](../../t-sql/queries/select-over-clause-transact-sql.md). ## 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. AVG is a deterministic function when used without the OVER and ORDER BY clauses. It is nondeterministic when specified with the OVER and ORDER BY clauses. For more information, see [Deterministic and Nondeterministic Functions](../../relational-databases/user-defined-functions/deterministic-and-nondeterministic-functions.md). ## 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 [!INCLUDE[ssSampleDBCoFull](../../includes/sssampledbcofull-md.md)] have used. Each of these aggregate functions produces a single summary value for all the retrieved rows. The example uses the [!INCLUDE[ssSampleDBnormal](../../includes/sssampledbnormal-md.md)] database. ```sql SELECT AVG(VacationHours)AS 'Average vacation hours', SUM(SickLeaveHours) AS 'Total sick leave hours' FROM HumanResources.Employee WHERE JobTitle LIKE 'Vice President%'; ``` [!INCLUDE[ssResult](../../includes/ssresult-md.md)] `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 territoryin the [!INCLUDE[ssSampleDBnormal](../../includes/sssampledbnormal-md.md)] 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. ```sql SELECT TerritoryID, AVG(Bonus)as 'Average bonus', SUM(SalesYTD) as 'YTD sales' FROM Sales.SalesPerson GROUP BY TerritoryID; GO ``` [!INCLUDE[ssResult](../../includes/ssresult-md.md)] ```sql 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 The following statement returns the average list price of productsin the [!INCLUDE[ssSampleDBnormal](../../includes/sssampledbnormal-md.md)] database. By specifying DISTINCT, only unique values are considered in the calculation. ```sql SELECT AVG(DISTINCT ListPrice) FROM Production.Product; ``` [!INCLUDE[ssResult](../../includes/ssresult-md.md)] `------------------------------` `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` tablein the [!INCLUDE[ssSampleDBnormal](../../includes/sssampledbnormal-md.md)] database including any duplicate values. ```sql SELECT AVG(ListPrice) FROM Production.Product; ``` [!INCLUDE[ssResult](../../includes/ssresult-md.md)] `------------------------------` `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 [!INCLUDE[ssSampleDBnormal](../../includes/sssampledbnormal-md.md)] 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. Notice that for `TerritoryID` 1, there are two rows for sales year 2005 representing the two sales people with sales that year. The average sales for these two rows is computed and then the third row representing sales for the year 2006 is included in the computation. ```sql 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; ``` [!INCLUDE[ssResult](../../includes/ssresult-md.md)] ```sql 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 does not include PARTITION BY. This means that the function will be applied 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 is applied. 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 rows of the query are displayed. ```sql 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; ``` [!INCLUDE[ssResult](../../includes/ssresult-md.md)] ```sql 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) ``` ## See also [Aggregate Functions (Transact-SQL)](../../t-sql/functions/aggregate-functions-transact-sql.md) [OVER Clause (Transact-SQL)](../../t-sql/queries/select-over-clause-transact-sql.md)