SQL AVG Function

AVG returns the average of the specified values.

AVG ignores NULL values.

Example

#

This example returns the average price of all products.

SELECT AVG(UnitPrice) AS Average
  FROM Product
Result:  1 record
Average
28.621282

Syntax

Syntax of the AVG function.

AVG(value)

value -- a number or column name.


More Examples

AVG with GROUP BY

ORDER
Id
OrderDate
OrderNumber
CustomerId
TotalAmount
Problem: List the monthly average sales for the year 2013.
SELECT MONTH(OrderDate) AS 'Month',
       AVG(TotalAmount) AS 'Average Monthly Sales'
  FROM [Order]
 WHERE YEAR(OrderDate) = 2013
 GROUP BY MONTH(OrderDate)
Result:  12 records
Month Average Monthly Sales
1 2020.993939
2 1420.937931
3 1332.663333
4 1796.754516
5 1775.740625

AVG with GROUP BY

PRODUCT
Id
ProductName
SupplierId
UnitPrice
Package
IsDiscontinued
SUPPLIER
Id
CompanyName
ContactName
City
Country
Phone
Fax
Problem: List the average product price by supplier.
SELECT S.CompanyName, 
       AVG(UnitPrice) AS 'Average Price'
  FROM Product P
  JOIN Supplier S ON S.Id = P.SupplierId
 GROUP BY S.CompanyName
Result:  29 records
CompanyName Average Price
Aux joyeux ecclésiastiques 140.750000
Bigfoot Breweries 15.333333
Cooperativa de Quesos 'Las Cabras' 29.500000
Escargots Nouveaux 13.250000
Exotic Liquids 15.666666

You may also like



Guides


vsn 3.1