MySQL average()

MySQL average() function example with examples on CRUD, insert statement, select statement, update statement, delete statement, use database, keys, joins etc.

<< Back to MYSQL

next → ← prev

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

Next TopicMySQL min()

← prev next →

.Net

.NET Array Dictionary List String 2D Async DataTable Dates DateTime Enum File For Foreach Format IEnumerable If IndexOf Lambda LINQ Parse Path Process Property Regex Replace Sort Split Static StringBuilder Substring Switch Tuple

Java

Core Array ArrayList HashMap String 2D Cast Character Console Deque Duplicates File For Format HashSet If IndexOf Lambda Math ParseInt Process Random Regex Replace Sort Split StringBuilder Substring Switch Vector While

TheDeveloperBlog.com

MySQL average()

MySQL avg() function

Syntax

Parameter explanation

MySQL avg() function example

Related Links:

.Net

Java

Related Links