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>
| 29.52.ratio_to_report |
| 29.52.1. | ratio_to_report |