019 SQL AVG

The current webpage explains the SQL AVG() function. Here’s a concise summary:

The AVG() function calculates the average value of a numeric column in an SQL table.

Basic Syntax:

SELECT AVG(column_name) FROM table_name WHERE condition;

Examples provided:
- Calculate the average price of all products:
```
SELECT AVG(Price) FROM Products;
```
- Calculate the average price only for products in a certain category using WHERE:
```
SELECT AVG(Price) FROM Products WHERE CategoryID = 1;
```
- Assign a custom name to the result using AS (alias):
```
SELECT AVG(Price) AS [average price] FROM Products;
```
- Return rows with values above the average using a subquery:
```
SELECT * FROM Products WHERE Price > (SELECT AVG(Price) FROM Products);
```
- Use with GROUP BY to find the average per group (e.g., category):
```
SELECT AVG(Price) AS AveragePrice, CategoryID FROM Products GROUP BY CategoryID;
```
Notes:
- NULL values are ignored in the average calculation.
- There are interactive “Try it Yourself” SQL editors for practicing the examples.
The page includes a quick exercise asking what the AVG function does, with correct answer: "Returns the average value of a numeric column".[1]

SQL AVG() Function

The AVG() function returns the average value of a numeric column.

Find the average price of all products:

SELECT AVG(Price)  
FROM Products;

Note: NULL values are ignored.

SELECT AVG(column_name)
FROM table_name
WHERE condition;

Below is a selection from the Products table used 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

You can add a WHERE clause to specify conditions:

Return the average price of products in category 1:

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

Give the AVG column a name by using the AS keyword.

Name the column "average price":

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

To list all records with a higher price than average, we can use the AVG() function in a sub query:

Return all products with a higher price than the average price:

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

Here we use the AVG() function and the GROUP BY clause, to return the average price for each category in the Products table:

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