SQL Server AVG() Function: Get the Average of Column Values

SQL Server AVG() function is an aggregate function that returns the average value of the specified column values.

The AVG() function computes the average of a set of given values by taking the sum of those values and dividing it by the count of non-null values.

AVG([ALL | DISTINCT] expression)

Parameters

expression: A valid expression that returns values of numeric data type. It can be a column of a table.

ALL is the default value that considers all values of expression/column.

DISTINCT considers only unique values to calculate the average.

Return Value

Expression	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

Example 1

In the following example, the AVG() is used with the Salary column of the Employee table. It calculates the average of all salaries and the average of distinct salaries.

Example: AVG()

SELECT AVG (ALL Salary) AS AllSalary, AVG (DISTINCT Salary) AS DistinctSalary 
FROM Employee

Example 2

In the following example, the AVG() function is used in the WHERE clause. It returns all employees whose salary is greater than the average salary.

Example: AVG() in WHERE Clause

SELECT EmployeeID, FirstName, Salary FROM Employee
WHERE Salary > (SELECT AVG(Salary) FROM Employee)