MySQL AVG Function

By Aastha Tyagi Last updated Jan 25, 2019 0

Introduction to MySQL AVG Function

This MySQL tutorial explains how to use the MySQL AVG Function with syntax and examples. The AVG() function returns the average value of a numeric column.

AVG() Syntax

SELECT AVG(column_name)
FROM table_name
WHERE condition;

To understand MySQL AVG Function, consider an “books” table, which is having the following records:

SELECT * FROM books;

BookId	BookName	Price	Author	publishedDate
1	Learning PHP, MySQL, and JavaScript	17	Robin Nixon	2017-02-02
2	Ubuntu: Up and Running	23	Robin Nixon	2017-03-23
3	PHP and MySQL Web Development	12	Luke Welling	2017-06-14
4	Murach's PHP and MySQL	14	Joel Murach	2017-06-17
5	Murach's Java Programming	62	Joel Murach	2017-07-28
6	Head first php mysql	22	Lynn Beighley	2017-07-31
7	Head first sql	11	Lynn Beighley	2017-09-10
8	HTML5 for IOS and Android: A Beginner's Guide	4	Robin Nixon	2017-09-12

AVG Function Example

Examples 1:

The following SQL statement finds the average price of all books:

SELECT AVG(Price) AS  "Avg Price"
FROM books;

Avg Price
20.6250

Below is a selection from the “employees” table, which is having the following records:

SELECT * FROM employees;

id	name	salary	department
1	Tom	4000	Technology
2	Sam	6000	Sales
3	Bob	3000	Technology
4	Alen	8000	Technology
5	Jack	12000	Marketing

Example 2: With Single Expression

Now suppose based on the above table you want to know how the average salary of all employees whose salary is above 6000.

SELECT AVG(salary) AS "Avg Salary"
FROM employees
WHERE salary > 6000;

Avg Salary
10000.0000

Example 3: Using GROUP BY

In some cases, you will be required to use the GROUP BY clause with the AVG function.

For example, you could also use the AVG function to calculate average salary for each department:

SELECT department, AVG(salary) AS "Avg salary"
FROM employees
GROUP BY department;

department	Avg salary
Marketing	12000.0000
Sales	6000.0000
Technology	5000.0000

MySql Function Salary