AVG function in MySQL

Introduction

The AVG function helps you find the average value of numbers in a column. It makes it easy to see the typical or middle value in a group of numbers.

You want to find the average score of students in a class.

You need to calculate the average price of products in a store.

You want to know the average temperature over a week.

You want to find the average salary of employees in a company.

Syntax

MySQL

SELECT AVG(column_name) FROM table_name;

AVG works only on numeric columns like integers or decimals.

It ignores NULL values when calculating the average.

Examples

Finds the average price of all products.

MySQL

SELECT AVG(price) FROM products;

Finds the average score for Math exams only.

MySQL

SELECT AVG(score) FROM exams WHERE subject = 'Math';

Calculates the average salary of employees in the Sales department.

MySQL

SELECT AVG(salary) FROM employees WHERE department = 'Sales';

Sample Program

This example creates a sales table with amounts. It inserts four rows, one with NULL. The AVG function calculates the average of the amounts, ignoring the NULL value.

MySQL

CREATE TABLE sales (
  id INT,
  amount DECIMAL(10,2)
);

INSERT INTO sales (id, amount) VALUES
(1, 100.00),
(2, 150.00),
(3, 200.00),
(4, NULL);

SELECT AVG(amount) AS average_amount FROM sales;

Important Notes

AVG ignores NULL values, so they do not affect the average calculation.

If all values are NULL, AVG returns NULL.

You can use AVG with GROUP BY to find averages for different groups.

Summary

AVG calculates the average of numeric values in a column.

It ignores NULL values automatically.

Useful for summarizing data like scores, prices, or salaries.