Avg function returns average of all the values in the
expression. It can be used with numeric type columns only. Null values are
ignored while performing average of an expression.
Syntax of AVG function :
AVG ( [
ALL | DISTINCT ] expression )
ALL is the default. It means all
values of the column or expression.
DISTINCT performs average of
unique values only.
Expression is avalid
expression in sql sever of type numeric except bit data type. It can be made up
of constant, column or function.
Examples of AVG function :
Example 1: Using avg function in SELECT clause
SELECT AVG(UnitsInStock) AS ‘Average of Stock’
FROM Products
Output
Average of Stock
40.8947368421053
Above example displays average of unit in stock using AVG
function.
Example 2 : Using avg function with DISTINCT
SELECT AVG ( DISTINCT UnitsInStock) AS ‘Average of Stock’
FROM Products
Output
Average of Stock
50.3
Above example performs average of unit in stock by taking
unique values of product using AVG function with DISTINCT clause.
Example 3 : Using avg function in SELECT clause with GROUP BY
clause
SELECT ProductName,AVG(Quantity)
AS ‘Average Product Quantity’
FROM Invoices
GROUP BY
ProductName
Output
ProductName Average Product
Quantity
Gula Malacca 25.04
Longlife Tofu 22.84
Ravioli Angelo 18.86
Mishi Kobe Niku 19
Queso Manchego La Pastora 24.57
Above example displays productwise average of quantity sold.