MySQL AVG Function

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;
1Learning PHP, MySQL, and JavaScript17Robin Nixon2017-02-02
2Ubuntu: Up and Running23Robin Nixon2017-03-23
3PHP and MySQL Web Development12Luke Welling2017-06-14
4Murach's PHP and MySQL14Joel Murach2017-06-17
5Murach's Java Programming62Joel Murach2017-07-28
6Head first php mysql22Lynn Beighley2017-07-31
7Head first sql11Lynn Beighley2017-09-10
8HTML5 for IOS and Android: A Beginner's Guide4Robin Nixon2017-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

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

SELECT * FROM employees;

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

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;
departmentAvg salary

Leave A Reply

Your email address will not be published.