sql_func_avg

SQL AVG() Function

The AVG() Function

The AVG() function is an aggregate function that calculates the average value of a numeric column.

NULL values in the specified column are ignored by the AVG() function during calculation.

SQL AVG() Syntax

SELECT AVG(column_name)
FROM table_name
WHERE condition;

The WHERE clause is optional. If omitted, AVG() calculates the average over all rows in the table for the specified column.

Demo Database

For this tutorial, we’ll use the Northwind sample database, specifically the “Products” table.

Below is a selection from the “Products” table:

ProductID
ProductName
SupplierID
CategoryID
Unit
Price


1
Chais
1
1
10 boxes x 20 bags
18.00


2
Chang
1
1
24 - 12 oz bottles
19.00


3
Aniseed Syrup
1
2
12 - 550 ml bottles
10.00


4
Chef Anton's Cajun Seasoning
2
2
48 - 6 oz jars
22.00


5
Chef Anton's Gumbo Mix
2
2
36 boxes
21.35

Note: Prices are simplified for clarity in this example.

SQL AVG() Examples

1. Calculate the average price of all products:

The following SQL statement calculates the average value of the “Price” column in the “Products” table.

SELECT AVG(Price) AS AverageProductPrice
FROM Products;

This query will return a single row with a single column named AverageProductPrice containing the calculated average.

2. Calculate the average price for products from a specific category (e.g., CategoryID = 1):

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

3. Using AVG() in a Subquery:

The following SQL statement selects all products whose price is higher than the average price of all products.

SELECT ProductName, Price
FROM Products
WHERE Price > (SELECT AVG(Price) FROM Products);

In this example, the subquery (SELECT AVG(Price) FROM Products) is executed first to determine the average price. Then, the outer query selects products exceeding this average.

Practical Application:

AVG() is widely used in reporting and data analysis to find mean values, such as average sales, average scores, average response times, etc. It’s often used with the GROUP BY clause to calculate averages for different groups within the data (e.g., average price per product category).