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;
BookIdBookNamePriceAuthor
1Learning PHP, MySQL, and JavaScript17Robin Nixon
2Ubuntu: Up and Running23Robin Nixon
3PHP and MySQL Web Development12Luke Welling
4Murach's PHP and MySQL14Joel Murach
5Murach's Java Programming62Joel Murach
6Head first php mysql22Lynn Beighley
7Head first sql11Lynn Beighley
8HTML5 for IOS and Android: A Beginner's Guide4Robin Nixon

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;
idnamesalarydepartment
1Tom4000Technology
2Sam6000Sales
3Bob3000Technology
4Alen8000Technology
5Jack12000Marketing

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;
departmentAvg salary
Marketing12000.0000
Sales6000.0000
Technology5000.0000

Leave A Reply

Your email address will not be published.

Comment moderation is enabled. Your comment may take some time to appear.