Oracle PL/SQL Tutorial/SQL PLUS Session Environment/ratio to report

ratio_to_report

SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
  2                    ENAME VARCHAR2(10),
  3                    JOB VARCHAR2(9),
  4                    MGR NUMBER(4),
  5                    HIREDATE DATE,
  6                    SAL NUMBER(7, 2),
  7                    COMM NUMBER(7, 2),
  8                    DEPTNO NUMBER(2));
Table created.
SQL>
SQL>
SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK",    7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7521, "WARD",  "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER",  7839, TO_DATE("2-APR-1981",  "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10);
1 row created.
SQL>
SQL> column pct_dept format 99.9
SQL> column pct_overall format 99.9
SQL> break on deptno skip 1
SQL>
SQL> select deptno,
  2         ename,
  3         sal,
  4         sum(sal) over (partition by deptno order by sal,ename) cum_sal,
  5         round(100*ratio_to_report(sal)
  6              over (partition by deptno), 1 ) pct_dept,
  7         round(100*ratio_to_report(sal) over () , 1 ) pct_overall
  8    from emp
  9   order by deptno, sal
 10  /
    DEPTNO ENAME             SAL    CUM_SAL PCT_DEPT PCT_OVERALL
---------- ---------- ---------- ---------- -------- -----------
        10 MILLER           1300       1300     14.9         4.5
           CLARK            2450       3750     28.0         8.4
           KING             5000       8750     57.1        17.2
        20 SMITH             800        800      7.4         2.8
           ADAMS            1100       1900     10.1         3.8
           JONES            2975       4875     27.4        10.2
           FORD             3000       7875     27.6        10.3
           SCOTT            3000      10875     27.6        10.3
        30 JAMES             950        950     10.1         3.3
    DEPTNO ENAME             SAL    CUM_SAL PCT_DEPT PCT_OVERALL
---------- ---------- ---------- ---------- -------- -----------
        30 MARTIN           1250       2200     13.3         4.3
           WARD             1250       3450     13.3         4.3
           TURNER           1500       4950     16.0         5.2
           ALLEN            1600       6550     17.0         5.5
           BLAKE            2850       9400     30.3         9.8

14 rows selected.
SQL>
SQL> drop table emp;
Table dropped.
SQL>
SQL>