We Will be looking at:
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:
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:
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.