SQL AVG/Average Function

SQL AVG FunctionOracle 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.


Tagged , , , , , , , , , , , . Bookmark the permalink.