MySQL Average

Syntax of MySQL AVG() Function

Let's take a look at the syntax used for applying MySQL average method:

Parameters of MySQL AVG() Function

As seen in the above syntax, expression or column_name is the only parameter or argument which is passed into the AVG() function. The parameter contains the data that we want to calculate the average for. The argument or parameter can be a column_name, an expression or it may be a combination of both.

Return Value of MySQL AVG() Function

The MySQL average method will return the average of the values inside a column or expression which is passed as an argument. The value returned by the AVG() method will be a numeric value with decimal precision.

Exceptions of MySQL AVG() Function

Generally, MySQL AVG() method runs without any errors or exceptions but still, there are scenarios where the MySQL average method throws exceptions and errors.

Let's discuss them in detail:

• One of the scenarios arises when a non-numeric value is passed as an argument or parameter then the AVG() function will throw an error which will display that the AVG() function will not be able to calculate the average of the given input due to the presence of non-numerical value.

• Another scenario occurs when the input column contains NULL values which will still be able to calculate the average of the given input but as discussed earlier there will be no null value taken into consideration during the calculation.

How does the MySQL AVG() Function work?

Aggregate functions are mathematical functions that are used to perform mathematical calculations in MySQL. The AVG() method is another aggregate function that is used to calculate the average of the expression or column passed as an argument or parameter in the AVG() function. The MySQL average method is used to return the average of the numerical data sets. The argument or parameter passed into the AVG() method as input should be numeric.

The AVG() method takes a column or an expression as input and returns the average of the values in that column combined.

As discussed earlier, the MySQL AVG() method takes only one parameter and ignores the NULL values in the input column or expression.

The MySQL AVG() function can be used with different functions and clauses such as the WHERE clause and the GROUP BY clause.

For example:

Let's consider a table Orders having columns as item_id, item_name, item_price. Here, we will find the average price of items in the Orders table using the AVG() function.

This query will return the average price of items in the Orders table.

Examples

Now, let's understand the practical usage of the MySQL AVG() method using different examples.

Example 1:

In this example, we will consider a table Employee which will have columns such as emp_id, emp_name, emp_dept and emp_salary from which we will calculate the average salary of all the employees combined.

Let's consider the following table Employee:

Query (For table creation):

Output

Now, Let's move towards structuring a query to find the average salary of the total employees in the Employee table.

Query (To find average salary):

Output

Explanation Here, we have used the MySQL average function AVG() to calculate the average salary of all the employees and departments combined as seen in the output table.

Example 2:

Let's consider the same table used in the previous example. Here also we will calculate the average salary of a department by using the WHERE clause.

Query:

Output

Explanation In this example, we calculated the average salary of employees in the IT department. The AVG() method is applied on the emp_salary column where the department is IT and that is why we get the average salary of all the employees combined working in the IT department.

Example 3:

In this example, let's consider the same Employee table. Here, we will find the average salary of employees working in each departmentGROUP BY clause.

Query:

Output

Explanation Here, we calculated the average salary of employees in each department. By using the MySQL average function AVG(), the department-wise average of salary is calculated which is grouped using the GROUP BY clause.

See Also

Also, look at some other aggregate functions related to the MySQL AVG() method:

• MySQL SUM().
• MySQL MAX().
• MySQL MIN().
• MySQL COUNT().