Mega Code Archive

 

Categories / Oracle PLSQL / Analytical Functions

 

RATIO_TO_REPORT, break on and compute

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> SQL> BREAK ON region SQL> SQL> COMPUTE sum of portion ON 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  ORDER BY region, curr_salary; ENAME      CURR_SALARY R  PORTION PORTION2 ---------- ----------- - -------- -------- Jode             29000 E    .1543    .1543 Alison           48000      .2553    .2553 Linda            53000      .2819    .2819 Celia            58000      .3085    .3085                        * --------                        s   1.0000 James            32000 W    .2092    .2092 Robert           36000      .2353    .2353 David            85000      .5556    .5556                        * --------                        s   1.0000 7 rows selected. SQL> SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee   2  / Table dropped. SQL> SQL> SQL>