using avreage (avg) function in oracle plsql

Oracle SQL Average Function or SQL AVG Function returns average of available records. Oracle Average Function or Oracle AVG Function accepts column/formula as parameter.

---

SQL Average Function Syntax

SELECT AVG(numeric column / formula)
FROM table_name
WHERE conditions;

---

SQL Average Function Examples

Suppose we have a table named “Employee” with the data as shown below:

Employee_Id	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
104	Emp D	30000	Support
105	Emp E	32000	Sales	10

We will learn using Oracle SQL AVG Function below.

SQL Average Function – Oracle AVG Simple Usage

Oracle AVG Function query below returns the average of salaries of all employees from ’employee’ table.

SELECT AVG(salary) "Oracle SQL Average"
FROM employee;

Above Oracle Average Function query returns ‘24000’ as the average of salaries from employee table.

Note: We have aliased AVG(Salary) as Oracle SQL Average.

SQL Average Function – SQL AVG Using Formula Example

Oracle SQL Average Function accepts formula for calculation.

The SQL AVG Function query below return Average of Salary*(commission/100) of ‘Sales’ department.

SELECT AVG(salary*(commission/100)) "SQL AVG"
FROM employee
WHERE department  = 'Sales';

Above Oracle Average Function query returns ‘2100’ as the average of Salary*(commission/100) of ‘Sales’ department from ’employee’ table.

Note: We have aliased AVG(salary*(commission/100)) as SQL AVG.

---

SQL Average Function – Using SQL Group By Clause

SQL AVG Function can be used with the SQL Group By Clause.

For example, the Oracle AVG Function query below return the average salary in each department.

SELECT department, AVG(salary) Total_Salary
FROM employee
GROUP BY department;

Above Oracle SQL Average Function query returns following data:

Department	Average_Salary
Sales	21000
IT	24000
Support	30000

As we can understand that by using SQL GROUP BY Clause with Oracle Average Function we can fetch average salary of employee(s) in every unique department.