Portion amounts add to 1.000 in each region : RATIO_TO_REPORT « Analytical Functions « Oracle PL / SQL






Portion amounts add to 1.000 in each 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> -- 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> 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> -- Portion amounts add to 1.000 in each region
SQL>
SQL> SELECT ename, curr_salary, region,
  2    curr_salary/SUM(curr_salary) OVER(PARTITION BY Region)
  3        Portion,
  4    RATIO_TO_REPORT(curr_salary) OVER(PARTITION BY Region)
  5        Portion2
  6  FROM employee
  7  UNION
  8  SELECT null, TO_NUMBER(null), region, sum(P1), sum(p2)
  9  FROM
 10    (SELECT ename, curr_salary, region,
 11         curr_salary/SUM(curr_salary) OVER(PARTITION BY Region) P1,
 12         RATIO_TO_REPORT(curr_salary) OVER(PARTITION BY Region) P2
 13    FROM employee)
 14  GROUP BY region
 15  ORDER BY 3,2;

ENAME      CURR_SALARY R  PORTION PORTION2
---------- ----------- - -------- --------
Jode             29000 E    .1543    .1543
Alison           48000 E    .2553    .2553
Linda            53000 E    .2819    .2819
Celia            58000 E    .3085    .3085
                       E   1.0000   1.0000
James            32000 W    .2092    .2092
Robert           36000 W    .2353    .2353
David            85000 W    .5556    .5556
                       W   1.0000   1.0000

9 rows selected.

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

Table dropped.

SQL>
SQL>
SQL>
SQL>
           
       








Related examples in the same category

1.RATIO_TO_REPORT Demo
2.RATIO_TO_REPORT can easily be partioned as well
3.Notice that the PORTION column adds up to 100%
4.RATIO_TO_REPORT, break on and compute
5.RATIO_TO_REPORT(): compute the ratio of a value to the sum of a set of values