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)


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)
Want to check how much you know SQL Server?