MySQL AVG() Function

The AVG() function calculates the arithmetic mean of all non-null values in a numeric column. It divides the total sum by the count of non-null rows automatically. Use it whenever you need to find the average price, score, salary, rating, or any other numeric measure across a set of rows.

---

Syntax

SELECT AVG(column_name) AS alias FROM table_name;

---

Sample Table

id	name	category	price	quantity_sold
1	Laptop	Electronics	55000.00	5
2	Phone	Electronics	25000.00	12
3	T-Shirt	Clothing	800.00	40
4	Headphones	Electronics	3500.00	18
5	Jeans	Clothing	2200.00	22
6	Tablet	Electronics	35000.00	8
7	Jacket	Clothing	4500.00	15

---

Basic AVG()

Calculate the average price across all products:

SELECT AVG(price) AS avg_price FROM products;
-- Returns: 18000.000000

MySQL often returns many decimal places. Use ROUND() to make the output cleaner:

SELECT ROUND(AVG(price), 2) AS avg_price FROM products;
-- Returns: 18000.00

---

AVG() with WHERE

Calculate the average for a filtered subset:

-- Average price of Electronics products only
SELECT ROUND(AVG(price), 2) AS avg_electronics_price
FROM products
WHERE category = 'Electronics';
-- Returns: 29625.00

-- Average quantity sold for products priced below 5000
SELECT ROUND(AVG(quantity_sold), 1) AS avg_sold_cheap
FROM products
WHERE price < 5000;
-- Returns: 25.7

---

AVG() with GROUP BY

Get the average price per category:

SELECT category, ROUND(AVG(price), 2) AS avg_price
FROM products
GROUP BY category;

category	avg_price
Electronics	29625.00
Clothing	2500.00

---

AVG() and NULL Values

Like all aggregate functions, AVG() ignores NULL values. The average is calculated only from the rows that have a real value — NULL rows are excluded from both the sum and the count.

-- If quantity_sold were NULL for one row, it is excluded:
-- AVG is calculated as: SUM(non-null values) / COUNT(non-null rows)

-- Treat NULL quantity_sold as 0 in the average
SELECT ROUND(AVG(COALESCE(quantity_sold, 0)), 2) AS avg_with_nulls_as_zero
FROM products;

---

Comparing AVG with MIN and MAX

Often useful to see all three together for a quick statistical summary:

SELECT
    ROUND(AVG(price), 2) AS avg_price,
    MIN(price)           AS min_price,
    MAX(price)           AS max_price
FROM products;

avg_price	min_price	max_price
18000.00	800.00	55000.00

---

Key Points to Remember

• AVG() returns the mean of all non-null values in a column.
• Use ROUND(AVG(column), n) to control the number of decimal places in the result.
• NULL values are ignored — they are not treated as zero.
• Use AVG(COALESCE(column, 0)) to count NULL as zero in the average.
• Combine with WHERE to average a filtered subset, and with GROUP BY for per-group averages.