← Back to Chapters

MySQL AVG() Function

? MySQL AVG()

? Quick Overview

The AVG() aggregate function in MySQL calculates the average value of a numeric column. It is commonly used for analytics, reports, and summaries, often combined with GROUP BY and WHERE.

? Key Concepts

  • Aggregate function: Computes a single result from multiple rows.
  • NULL handling: AVG() ignores NULL values.
  • Return type: Produces a floating-point value.
  • Grouping: Works with GROUP BY to calculate per-group averages.

? Syntax / Theory

? View Code Example 
-- Calculate average for all rows in a column
SELECT AVG(column_name) FROM table_name;
? View Code Example 
-- Calculate average value per group
SELECT class, AVG(score) AS avg_score
FROM exam_results
GROUP BY class;

? Code Example(s)

? View Code Example 
-- Create table and calculate overall average
CREATE TABLE Scores (
id INT PRIMARY KEY,
student VARCHAR(100),
points INT
);

INSERT INTO Scores (id, student, points) VALUES
(1,'Alice',85),
(2,'Bob',90),
(3,'Charlie',NULL),
(4,'Diana',75);

SELECT AVG(points) AS average_points FROM Scores;
? View Code Example 
-- Calculate average order amount per customer
CREATE TABLE Orders (
order_id INT PRIMARY KEY,
customer_id INT,
amount DECIMAL(8,2)
);

INSERT INTO Orders VALUES
(1,1,150.00),
(2,1,200.00),
(3,2,50.00),
(4,2,100.00);

SELECT customer_id, AVG(amount) AS avg_order_amount
FROM Orders
GROUP BY customer_id;
? View Code Example 
-- Control precision and handle NULL values
SELECT ROUND(AVG(amount),2) AS avg_two_decimals FROM Orders;
SELECT AVG(COALESCE(amount,0)) AS avg_with_nulls FROM Orders;

? Live Output / Explanation

AVG(points) ignores NULL values, so the result is based only on valid numeric rows.

Grouped averages return one row per group, such as per customer or class.

ROUND() helps format results for reports and dashboards.

? Tips & Best Practices

  • Use COALESCE() if NULL values must be treated as zero.
  • Apply ROUND() or CAST() for consistent formatting.
  • Filter rows early using WHERE for better performance.
  • Use HAVING to filter aggregated results.

? Try It Yourself / Practice Tasks

  1. Find average salary per department.
  2. List products with average price above a threshold.
  3. Compare averages with and without NULL handling.
  4. Format averages to one decimal place.