AVG returns the average of the specified values.
AVG ignores NULL values.
This example returns the average price of all products.
SELECT AVG(UnitPrice) AS Average
FROM Product
| Average |
|---|
| 28.621282 |
Syntax of the AVG function.
AVG(value)
value -- a number or column name.
| ORDER |
|---|
| Id |
| OrderDate |
| OrderNumber |
| CustomerId |
| TotalAmount |
SELECT MONTH(OrderDate) AS 'Month',
AVG(TotalAmount) AS 'Average Monthly Sales'
FROM [Order]
WHERE YEAR(OrderDate) = 2013
GROUP BY MONTH(OrderDate)
| Month | Average Monthly Sales |
|---|---|
| 1 | 2020.993939 |
| 2 | 1420.937931 |
| 3 | 1332.663333 |
| 4 | 1796.754516 |
| 5 | 1775.740625 |
 |
|
| PRODUCT |
|---|
| Id |
| ProductName |
| SupplierId |
| UnitPrice |
| Package |
| IsDiscontinued |
| SUPPLIER |
|---|
| Id |
| CompanyName |
| ContactName |
| City |
| Country |
| Phone |
| Fax |
SELECT S.CompanyName,
AVG(UnitPrice) AS 'Average Price'
FROM Product P
JOIN Supplier S ON S.Id = P.SupplierId
GROUP BY S.CompanyName
| 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 |
|
|
