See the difference between a person's salary and the average for his or her region : Analytical Functions Basics « Analytical Functions « Oracle PL / SQL






See the difference between a person's salary and the average for his or her region



SQL> -- create demo table
SQL> create table Employee(
  2    empno              Number(3)  NOT NULL, -- Employee ID
  3    ename              VARCHAR2(10 BYTE),   -- Employee Name
  4    hireDate          DATE,                -- Date Employee Hired
  5    orig_salary        Number(8,2),         -- Orignal Salary
  6    curr_salary        Number(8,2),         -- Current Salary
  7    region             VARCHAR2(1 BYTE)     -- Region where employeed
  8  )
  9  /

Table created.

SQL>
SQL>
SQL> -- prepare data for employee table
SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(122,'Alison',to_date('19960321','YYYYMMDD'), 45000,       48000,       'E')
  3  /

1 row created.

SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(123, 'James',to_date('19781212','YYYYMMDD'), 23000,       32000,       'W')
  3  /

1 row created.

SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(104,'Celia',to_date('19821024','YYYYMMDD'), 53000,       58000,        'E')
  3  /

1 row created.

SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(105,'Robert',to_date('19840115','YYYYMMDD'), 31000,      36000,        'W')
  3  /

1 row created.

SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(116,'Linda', to_date('19870730','YYYYMMDD'), 43000,       53000,       'E')
  3  /

1 row created.

SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(117,'David', to_date('19901231','YYYYMMDD'), 78000,       85000,       'W')
  3  /

1 row created.

SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(108,'Jode',  to_date('19960917','YYYYMMDD'), 21000,       29000,       'E')
  3  /

1 row created.

SQL>
SQL>
SQL> select * from employee;

     EMPNO ENAME      HIREDATE  ORIG_SALARY CURR_SALARY R
---------- ---------- --------- ----------- ----------- -
       122 Alison     21-MAR-96       45000       48000 E
       123 James      12-DEC-78       23000       32000 W
       104 Celia      24-OCT-82       53000       58000 E
       105 Robert     15-JAN-84       31000       36000 W
       116 Linda      30-JUL-87       43000       53000 E
       117 David      31-DEC-90       78000       85000 W
       108 Jode       17-SEP-96       21000       29000 E

7 rows selected.

SQL>
SQL> -- See the difference between a person's salary and the average for his or her region
SQL>
SQL>
SQL>
SQL> SELECT empno, ename, region, curr_salary,
  2    orig_salary,
  3    ROUND(AVG(orig_salary) OVER(PARTITION BY region))
  4        "Avg-group",
  5    ROUND(orig_salary - AVG(orig_salary) OVER(PARTITION
  6        BY region)) "Diff."
  7  FROM employee
  8  ORDER BY region, ename;

     EMPNO ENAME      R CURR_SALARY ORIG_SALARY  Avg-group      Diff.
---------- ---------- - ----------- ----------- ---------- ----------
       122 Alison     E       48000       45000      40500       4500
       104 Celia      E       58000       53000      40500      12500
       108 Jode       E       29000       21000      40500     -19500
       116 Linda      E       53000       43000      40500       2500
       117 David      W       85000       78000      44000      34000
       123 James      W       32000       23000      44000     -21000
       105 Robert     W       36000       31000      44000     -13000

7 rows selected.

SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
  2  /

Table dropped.

SQL>
SQL>
SQL>
           
       








Related examples in the same category

1.The direct application of a WHERE clause in the query is not allowed
2.Use multiple analytical functions
3.Combine analytical function with count(*)
4.Adding an Analytical Function to a Query that Contains a Join (and Other WHERE Conditions)
5.Adding an Analytical Function to the GROUP BY with ORDER BY Version
6.Changing the Final Ordering after Having Added an Analytical Function
7.Use an analytical function in a WHERE clause
8.More Than One Analytical Function May Be Used in a Single Statement
9.If more analytical functions are added, yet more sorting may result
10.Nulls could also be handled with a default value using the NVL function in the analytical function
11.Column alias for the analytical function is used in the final ORDER BY