SQL Server AVG() Function

AVG() calculates the average of numeric expression values and ignores NULLs. Its return type depends on the input type. See Microsoft’s AVG() reference.

Syntax

AVG([ ALL | DISTINCT ] expression)

ALL is the default and includes duplicate values; DISTINCT averages only unique values. The expression must be an exact or approximate numeric type other than bit.

Return type and overflow

The result type follows the expression type:

Input type	Return type
`tinyint`, `smallint`, `int`	`int`
`bigint`	`bigint`
`decimal(p, s)`, `numeric(p, s)`	`decimal(38, max(s, 6))`
`money`, `smallmoney`	`money`
`float`, `real`	`float`

Because AVG(int_expression) returns int, it cannot return fractional results. Cast integer inputs to a suitable decimal before calling AVG() when a fractional average is required. AVG() divides the sum by the count of non-NULL values; if that sum exceeds the return type’s range, SQL Server raises an error. See SQL Server Error 8115: Arithmetic Overflow.

Examples

Here are two examples of using the AVG() function.

Example 1

Suppose you have a student score table as follows:

id	name	score
1	Tom	85
2	Jack	90
3	Alice	80
4	Bob	95

You can use the following SQL statement to calculate the average score of the students:

SELECT AVG(CONVERT(decimal(10, 2), score)) AS avg_score
FROM student_scores;

The result is:

avg_score
87.500000

Example 2

Suppose you have an order table as follows:

order_id	customer_id	order_total
1	1001	50
2	1002	75
3	1001	100
4	1003	125

You can use the following SQL statement to calculate the average order total:

SELECT AVG(CONVERT(decimal(10, 2), order_total)) AS avg_total
FROM orders;