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)