MySQL AVG Function

Back to: MySQL Tutorials for Beginners and Professionals

MySQL AVG Function with Examples

In this article, I am going to discuss MySQL AVG Function with Examples. Please read our previous article where we discussed MySQL MIN and MAX Function with Examples.

Understanding AVG Function in MySQL:

We are going to use the following Employee table to understand the MySQL AVG function with Examples.

Please use the following SQL Script to create and populate the Employee table with the required sample data.

CREATE DATABASE Company;
USE Company;

CREATE TABLE Employee (
  Id INT PRIMARY KEY,
  Name VARCHAR(45) NOT NULL,
  Department VARCHAR(45) NOT NULL,
  Salary FLOAT NOT NULL,
  Gender VARCHAR(45) NOT NULL,
  Age INT NOT NULL,
  City VARCHAR(45) NOT NULL
);

INSERT INTO Employee VALUES (1001, 'John Doe', 'IT', 35000, 'Male', 25, 'London');
INSERT INTO Employee VALUES (1002, 'Mary Smith', 'HR', 45000, 'Female', 27, 'London');
INSERT INTO Employee VALUES (1003, 'James Brown', 'Finance', 50000, 'Male', 28, 'London');
INSERT INTO Employee VALUES (1004, 'Mike Walker', 'Finance', 50000, 'Male', 28, 'London');
INSERT INTO Employee VALUES (1005, 'Linda Jones', 'HR', 75000, 'Female', 26, 'London');
INSERT INTO Employee VALUES (1006, 'Anurag Mohanty', 'IT', 35000, 'Male', 25, 'Mumbai');
INSERT INTO Employee VALUES (1007, 'Priyanla Dewangan', 'HR', 45000, 'Female', 27, 'Mumbai');
INSERT INTO Employee VALUES (1008, 'Sambit Mohanty', 'IT', 50000, 'Male', 28, 'Mumbai');
INSERT INTO Employee VALUES (1009, 'Pranaya Kumar', 'IT', 50000, 'Male', 28, 'Mumbai');
INSERT INTO Employee VALUES (1010, 'Hina Sharma', 'HR', 75000, 'Female', 26, 'Mumbai');

MySQL AVG Function

The MySQL AVG or AVERAGE function returns the average value of the given numeric column. The AVG function will only work on numeric columns. For columns containing string values, it will always return 0.

SELECT AVG(columnname) FROM tablename;

Example: AVG Function with Numeric Column in MySQL

Our requirement is to find the Average age from the Employees table. To do so, we need to pass the Age column to the AVG function as shown in the below SQL query.

SELECT AVG(Age) AS AVG_AGE FROM Employee;

When you execute the above SELECT Statement, you will get the following output. Here, we get the average age value of the employee table.

Example: AVG function with GROUP by Clause in MySQL

Now we need to find the average salary of each department. To do the same, we need to pass the salary column to the AVG function as well as we need to group the employees by the department by using the GROUP by clause as shown in the below query.

SELECT Department, AVG(Salary) AS AVG_Salary
FROM Employee
GROUP BY Department;

When you execute the above SELECT query, you will get the following output.

Example: AVG Function with WHERE Clause in MySQL

Now we need to find the average salary of all Female Employees. To do so, we need to pass the Salary column to the AVG function as well as filter the Female employees by using the WHERE Filter clause as shown in the below query.

SELECT AVG(Salary) AS AVG_Salary
FROM Employee
WHERE Gender = 'Female';

When you execute the above SELECT query, you will get the following output.

Example: AVG Function with String Values in MySQL

If we pass the string column value to the AVG function, then we will get 0 as the result. In the below query, we are passing the Name column to the AVG function.

SELECT AVG(Name) As AVG_Name FROM Employee;

Now when you execute the above SQL query, you will get the following output.

In the next article, I am going to discuss MySQL UCASE and LCASE Functions with Examples. Here, in this article, I try to explain the MySQL AVG Function with Examples. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.