Oracle 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

Advertisements

Author: sqlandplsql

To learn more about the Oracle, SQL, PL SQL, Performance Tuning, Database Modeling, Ubuntu, MySQL etc .Suggestions, comments, feedbacks and referrals are highly appreciated.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s