SQL AVG function

AVG function returns the average from the expression. It is an example of aggregate functions.

Syntax:

select avg(column_name) from table_name;

Examples:-

Consider below EMP table structure

Empno	Empname	Salary	Deptno
10	Bill	12000	5
11	Solomon	10000	5
12	Susan	10000	5
13	Wendy	9000	1
14	Benjamin	7500	1
15	Tom	7600	1
16	Henry	8500	2
17	Robert	9500	2
18	Paul	7700	2

1. Find the average salary among the employees.

select avg(salary) from EMP;     

AVG(SALARY)

------------------------

      9088.88889

2. Find the average salary among the employees in a department (Using where clause). 

select avg(salary) from EMP where deptno = 5;     

AVG(SALARY)

------------------------

      10666.6667

3. Find the average salary in each department (Using group by clause). 

select deptno,avg(salary) from EMP group by deptno;     

 DEPTNO  AVG(SALARY)

 ----------     ----------------------

 1              8033.33333
  2              8566.66667
  5              10666.6667

4. Find the all department details having average salary higher than 9000 (Using having by clause). 

select deptno,avg(salary) from EMP group by deptno having avg(salary) > 9000;


DEPTNO AVG(SALARY)
----------    ---------------------
 5             10666.6667