Oracle SQL - To see the difference between a person's salary and the average for his or her region.

  1. HOME
  2. Oracle SQL
  3. Analytic Functions
  4. Partitions

Description

To see the difference between a person's salary and the average for his or her region.

Demo

SQL>
SQL> drop table emp;

Table dropped.-- from  ww  w . ja  v a 2  s  . co  m

SQL> create table emp(
  2  empno        Number(3)  NOT NULL,
  3  ename        VARCHAR2(10 BYTE),
  4  hireDate     DATE,
  5  orig_salary  Number(8,2),
  6  curr_salary  Number(8,2),
  7  region       VARCHAR2(1 BYTE)
  8  );
SQL>
SQL> insert into emp values(1,'Alison',DATE '1996-03-21', 45000, NULL,  'E');
SQL> insert into emp values(2,'James',DATE  '1978-12-12', 23000, 32000, 'W');
SQL> insert into emp values(3,'Celia',DATE  '1982-10-24', NULL,  58000, 'E');
SQL> insert into emp values(4,'Robert',DATE '1984-01-15', 31000, NULL,  'W');
SQL> insert into emp values(5,'Linda',DATE  '1987-07-30', NULL,  53000, 'E');
SQL> insert into emp values(6,'David',DATE  '1990-12-31', 78000, NULL,  'W');
SQL> insert into emp values(7,'Jode',DATE  '1996-09-17', 21000, 29000, 'E');
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 emp
  8  ORDER BY region, ename
  9

Related Topic