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.
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.
SELECT EmployeeID, FirstName, Salary FROM Employee
WHERE Salary > (SELECT AVG(Salary) FROM Employee)
