MySQL AVG() Function

The AVG() function calculates the average (arithmetic mean) of a numeric column or expression. It ignores NULL values when computing the result.

Syntax

AVG(expression)

Definition and Usage

• The AVG() function returns the average of the specified numeric values.
• NULL values are ignored in the calculation.
• It is commonly used with GROUP BY to calculate averages per group.

Parameter Values

Technical Details

• Works in: From MySQL 4.0
• Result: Returns a numeric value representing the average.

Examples

Example 1: Average value of a column

Calculate the average price in the Products table:

SELECT AVG(Price) AS AveragePrice 
FROM Products;

Result:

AveragePrice: 50.75

Example 2: Filter records based on the average value

Select products with prices above the average:

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

Result:

ProductID   Name      Price
2           Laptop    80.00
4           Monitor   70.00

Example 3: Use AVG() with GROUP BY

Calculate the average salary for employees grouped by department:

SELECT DepartmentID, AVG(Salary) AS AverageSalary
FROM Employees
GROUP BY DepartmentID;

Result:

DepartmentID   AverageSalary
1              5000.00
2              6200.00
3              4800.00

Example 4: Include a formula in AVG()

Find the average of a computed column:

SELECT AVG(Price * Quantity) AS AverageTotal
FROM Orders;

Result:

AverageTotal: 125.50

Usage Notes

1. NULL Handling:

   • NULL values in the column are ignored when calculating the average.
   • If all values are NULL, the result is NULL.

2. Precision:

   • The result may include decimal places depending on the data type and values.

3. Combining with Other Functions:

   • You can combine AVG() with other aggregate functions like SUM(), MIN(), MAX() for advanced analytics.

4. Performance Tip:

   • When used with large datasets, consider indexing the column being averaged for faster query execution.

Practical Query

To calculate the average price of products per category and sort by the highest average:

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

Result:

CategoryID   AveragePrice
3            85.50
1            60.00
2            45.25