AVG (Transact-SQL)

Calculates the average of an expression that you specify.

Syntax:

AVG ( [ ALL | DISTINCT ] <expression> ) [ OVER ( [ <partition_by_clause> ] ) ]

Parameters

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

DISTINCT
Specifies that the AVG function operates on the distinct values of the expression.

<expression>
An expression of the numeric data type category. It can only contain scalar values. AVG does not support the grouping of expressions.

OVER [ ( <partition_by_clause> ) ]
Indicates that the AVG function is applied as an analytic function to rows returned by the query. The OVER clause partitions the result set of the FROM clause into partitions to which the AVG function is applied.

<partition_by_clause>
PARTITION BY <expression1> [, ... ]
Divides the rows in the query result set into partitions. The aggregate function is applied to each partition separately and is reset for each partition. If PARTITION BY is not specified, the analytic function treats the entire result set as a single partition.

Return Type

Returns the average of the values in the specified expression, as the same data type as the input expression (or the data type with the highest precision if the expression has different numeric types). If the input is an empty set, AVG returns NULL. If the input contains boolean values, the behavior is undefined.

Description

The AVG function computes the average of all values in a given column or expression. It ignores NULL values during the calculation. If all values in the set are NULL, AVG returns NULL.

Examples

Example 1: Basic Usage

Find the average salary of all employees.

SELECT AVG(Salary) AS AverageSalary
FROM Employees;

If the Employees table contains the following data:

EmployeeID | Salary
-----------|-------
1          | 50000
2          | 60000
3          | 55000
4          | NULL

The query would return:

AverageSalary
-------------
55000.00

(50000 + 60000 + 55000) / 3 = 55000

Example 2: Using DISTINCT

Find the average of distinct salaries.

SELECT AVG(DISTINCT Salary) AS AverageDistinctSalary
FROM Employees;

Using the same data as Example 1, this would return:

AverageDistinctSalary
---------------------
55000.00

Example 3: Using OVER clause (Analytic Function)

Calculate the average salary for each department.

SELECT
    EmployeeID,
    Department,
    Salary,
    AVG(Salary) OVER (PARTITION BY Department) AS AvgSalaryInDepartment
FROM
    Employees;

If Employees table also includes a Department column:

EmployeeID | Department | Salary
-----------|------------|-------
1          | IT         | 50000
2          | HR         | 60000
3          | IT         | 55000
4          | HR         | 70000