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 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

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.