AVG (TSQL Function)

Returns the average value of the column.

Syntax

AVG ( expression )

Arguments

expression

is a numeric expression of type bit, tinyint, smallint, int, bigint, money, numeric, float.

Return Types

For integer expression, returns bigint.

For money, numeric or float expression, returns the same type.

Remarks

NULL values are ignored.

AVG(expression) is in fact replaced by SUM(expression) / COUNT(expression)

Examples

DECLARE @t TABLE (
    id         INT NOT NULL PRIMARY KEY,
    category   VARCHAR(30),
    price      NUMERIC(12, 2)
);

INSERT INTO @t VALUES (10, 'book',     123.50),
                      (20, 'fruit',    200.15),
                      (40, 'book',      21.40),
                      (50, 'fruit',     60.80),
                      (30, 'clothing', 300),
                      (60, 'book',      NULL)

SELECT category, SUM(price) as sum, AVG(price) as avg, SUM(price)/COUNT(price) as sum_count, SUM(price)/COUNT(*) as sum_count_star
FROM @t
GROUP BY category
ORDER BY AVG(price);

The result is:

category   |sum              |avg                   |sum_count              |sum_count_star         |
-----------+-----------------+----------------------+-----------------------+-----------------------+
book       |           144.90|       72.450000000000|        72.450000000000|        48.300000000000|
fruit      |           260.95|      130.475000000000|       130.475000000000|       130.475000000000|
clothing   |           300.00|      300.000000000000|       300.000000000000|       300.000000000000|

(3 row(s) affected)