How to Use AVG() Function in MySQL?

In MySQL, the AVG() function plays a crucial role in performing calculations on numerical data of database tables. It allows you to easily calculate the average value of a specific column, providing valuable insights into the central tendency of the data. Whether you need to find the average salary of employees, determine the average price of products, or analyze other numerical attributes, the AVG() function proves to be a powerful tool in MySQL queries.

This guide will present in-depth information on how to use the AVG() function in MySQL.

How to Use AVG() Function in MySQL?

The “AVG()” function in MySQL can be utilized with the “SELECT” statement as given below in the syntax:

SELECT AVG([column_name]) FROM [TABLE_NAME];

Let’s move to the examples but before that make sure you have logged in to the MySQL server.

Example 1: Calculating Average Using AVG() Function

After the login, confirm the table’s data before taking the average of any particular column as given below in the example:

SELECT * FROM linuxhint_employees;

Output

The output showed that the table’s data had been printed, and as marked in the screenshot, the “salary” column will be used for the example of the “AVG()” function.

So, an example to get the average of the “salary” column of the “linuxhint_employees” table is provided below:

SELECT AVG(salary) FROM linuxhint_employees;

Output

The output displayed the “salary” column’s average.

Example 2: How to Apply the AVG() Function on Filtered Data of a Particular Table?

To apply the “AVG()” function on filtered data of a particular table, you need to use the “WHERE” clause. An example of taking the average, after filtering the data of the “salary” column of the “linuxhint_employees” table is given below:

SELECT AVG(salary) FROM linuxhint_employees
WHERE department = 'Marketing';

Output

The output displayed the average of a particular column after filtering the data.

Example 3: How to Use AVG() Function in MySQL With a Subquery?

To filter any particular data using the “AVG()” function, you need to use the “AVG()” function as a subquery in the “WHERE” clause.

Let’s print the “linuxhint_products” table’s data before applying the “AVG()” function on any column:

SELECT id, name, price FROM linuxhint_products;

Output

The output showed that the table data of the “linuxhint_products” has been fetched.

Now the example of filtering the “linuxhint_products” table’s data using the “AVG()” function is given below:

SELECT id, name, price
FROM linuxhint_products
WHERE
price > (SELECT AVG(price) FROM linuxhint_products);

In the above example, the condition is to show all the rows that have a greater “price” value than the average of the “price” column of the “linuxhint_products” table.

Output

The output showed the only rows that have greater “price” value than the average of the “price” column.

Conclusion

The “AVG()” function in MySQL is a valuable tool for calculating the average of a particular column’s values in a table. By utilizing the “SELECT” statement with the AVG() function, you can easily obtain the average value. Additionally, the AVG() function can be applied to filtered data using the “WHERE” clause, or you can use the AVG() function as a subquery to filter data based on the average value. This article has explained in-depth information on how to use the MySQL AVG() function.

How to Use AVG() Function in MySQL?