AVG Function in Oracle

Back to: Oracle Tutorials for Beginners and Professionals

AVG Function in Oracle with Examples

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

AVG Function in Oracle

The AVG or AVERAGE function in Oracle is used to return the average value of the given numeric column. The AVG function will only work on numeric columns. For columns containing string values, it will give an error i.e. Invalid Number. Following is the syntax to use AVG function in Oracle.

SELECT AVG(columnname) FROM tablename;

Examples to Understand AVG Function in Oracle:

We are going to use the following Employee table to understand the need and use of AVG Function in Oracle with Examples.

Please execute the below SQL query to drop the existing Employee table and create a new Employee table with the required sample data.

DROP Table Employee;

CREATE TABLE Employee (
  Id INT PRIMARY KEY,
  Name VARCHAR(15),
  Department VARCHAR(10),
  Salary NUMBER(8, 2),
  Gender VARCHAR(10),
  Age INT,
  City VARCHAR(10)
);

INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1001, 'John', 'IT', 35000, 'Male', 25, 'London');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1002, 'Smith', 'HR', 45000, 'Female', 27, 'Mumbai');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1003, 'James', 'Finance', 50000, 'Male', 28, 'Delhi');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1004, 'Mike', 'Finance', 50000, 'Male', 28, 'London');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1005, 'Linda', 'HR', 75000, 'Female', 26, 'Mumbai');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1006, 'Anurag', 'IT', 35000, 'Male', 25, 'London');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1007, 'Priyanla', 'HR', 45000, 'Female', 27, 'Mumbai');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1008, 'Sambit', 'IT', 50000, 'Male', 28, 'London');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1009, 'Pranaya', 'IT', 50000, 'Male', 28, 'London');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1010, 'Hina', 'HR', 75000, 'Female', 26, 'Mumbai');

AVG Function with Numeric Column in Oracle

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

SELECT AVG(Salary) AS AVG_Salary FROM Employee;

When you execute the above SELECT Statement, you will get the following output. Here, we get the average Salary value from the Employee table.

AVG function with GROUP by Clause in Oracle

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.

AVG Function with WHERE Clause in Oracle

Now we need to find the average salary among all the Female Employees. To do so, we need to pass the Salary column to the AVG function as well as we also need to 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.

AVG Function with String Values in Oracle

If we pass the string column value to the AVG function in Oracle, then we will get an error saying Invalid Number. 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 error.

In the next article, I am going to discuss JOINs in Oracle with Examples. Here, in this article, I try to explain AVG Function in Oracle with Examples and I hope you enjoy this AVG Function in Oracle with Examples article.