avg() function

The MySQL avg() is an aggregate function used to return the average value of an expression in various records.

Syntax

The following are the basic syntax an avg() function in MySQL:

SELECT AVG(aggregate_expression)    

FROM tables    

[WHERE conditions]; 

Parameter explanation

aggregate_expression: It specifies the column or expression that we are going to find the average result.

table_name: It specifies the tables from where we want to retrieve records. There must be at least one table listed in the FROM clause.

WHERE conditions: It is optional. It specifies the conditions that must be fulfilled for the records to be selected.

MySQL avg() function example

Consider our database has a table named employees, having the following data. Now, we are going to understand this function with various examples:

1. Basic Example

Execute the following query that calculates the average working hours of all employees in the table:

mysql> SELECT AVG(working_hours) Avg_working_hours FROM employees;  

Output:

We will get the result as below:

2. MySQL AVG() function with WHERE clause

The WHERE clause specifies the conditions that must be fulfilled for the selected records. Execute the following query to calculate the total average working hours of employees whose working_hours >= 12.

mysql> SELECT AVG(working_hours) Avg_working_hours FROM employees WHERE working_hours>=12;  

Output:

It will give the following output:

3. MySQL AVG() function with GROUP BY clause

The GROUP BY clause is used to return the result for each group by one or more columns. For example, this statement calculates the average working hours of each employee using the AVG() function and then group the result with the GROUP BY clause:

mysql> SELECT emp_name, occupation, AVG(working_hours) Avg_working_hours FROM employees GROUP BY occupation;  

Output:

Here, we can see that the total working hours of each employee calculates by grouping them based on their occupation.

4. MySQL AVG() function with HAVING clause

The HAVING clause is used to filter the average values of the groups in MySQL. Execute the following statement that calculates the average working hours of all employees, grouping them based on their occupation and returns the result whose Avg_working_hours>9.

  mysql> SELECT emp_name, occupation,   

AVG(working_hours) Avg_working_hours   

FROM employees   

GROUP BY occupation   

HAVING AVG(working_hours)>9;  

Output:

5. MySQL AVG() function with DISTINCT clause

MySQL uses the DISTINCT keyword to remove the duplicate rows from the column name. This clause is used with this avg() function to return the average value of a unique number of records present in the table.

Execute the following query that removes the duplicate records in the working_hours column of the employee table and then returns the average value:

  mysql> SELECT emp_name, occupation,   

AVG(DISTINCT working_hours) Avg_working_hours  

FROM employees  

GROUP BY occupation;

Output: