SQL AVG() Function

The AVG() function in SQL calculates the average (mean) of a set of numeric values. It's a fundamental aggregate function, used to summarize numerical data.

AVG(): Definition and Usage

AVG() is incredibly useful for understanding the central tendency of your data. It works on a set of values (typically from a single column in a table), ignoring any NULL values. The result is a single number representing the average of the non-null values.

Syntax

SELECT AVG(column_name)
FROM table_name
WHERE condition;
      

Example Database: Products Table

The following examples use this sample 'Products' table:

Examples

Calculating the Average Price of All Products

This query calculates the average price of all products.

SELECT AVG(Price) FROM Products;
      

17.67
      

Calculating the Average Price with a Condition

This calculates the average price for products in category 1.

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

18.5
      

Using an Alias

This gives the average price a more descriptive name.

SELECT AVG(Price) AS "Average Price" FROM Products;
      

Average Price
-------------
17.67
      

Finding Products Above Average Price

This uses AVG() in a subquery to identify products priced above average.

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

ProductID | ProductName              | SupplierID | CategoryID | Unit                     | Price
---------------------------------------------------------------------------------
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
      

Using AVG() with GROUP BY

This calculates the average price for each category.

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

AveragePrice | CategoryID
-------------|------------
18.5         | 1
16.675       | 2