SQL AVG

The SQL AVG() Function

The AVG() function provides the mean value of a numeric column.

Example

Calculate the mean price of all products.

SELECT AVG(Price)
FROM Products;

Note: NULL values are not taken into account.

Syntax

SELECT AVG(column_name)

  FROM table_name

  WHERE condition;

Demo Database

Here is a snippet from the Products table utilized in the examples:

ProductID	ProductName	SupplierID	CategoryID	Unit	Price
1	Chais	1	1	10 boxes x 20 bags	18
2	Chang	1	1	24 – 12 oz bottles	19
3	Aniseed Syrup	1	2	12 – 550 ml bottles	10
4	Chef Anton’s Cajun Seasoning	2	2	48 – 6 oz jars	22
5	Chef Anton’s Gumbo Mix	2	2	36 boxes	21.35

Add a WHERE Clause

To specify conditions, you can include a WHERE clause.

Example

Retrieve the mean price of products within category 1.

SELECT AVG(Price)
FROM Products
WHERE CategoryID = 1;

Use an Alias

Assign a name to the AVG column using the AS keyword.

Example

Label the column as “average price”.

SELECT AVG(Price) AS [average price]
FROM Products;

Higher Than Average

For listing all records with prices higher than the average, employing the AVG() function within a subquery is viable.

Example

Retrieve all products that have a price higher than the average price.

SELECT * FROM Products
WHERE price > (SELECT AVG(price) FROM Products);

Use AVG() with GROUP BY

In this instance, we utilize the AVG() function along with the GROUP BY clause to yield the average price for each category within the Products table.

Example

SELECT AVG(Price) AS AveragePrice, CategoryID
FROM Products
GROUP BY CategoryID;