Using a Column Multiple Times in a ROLLUP Clause

Using a column many times in a ROLLUP clause get report on different groupings of data.


CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
                  ENAME VARCHAR2(10),
                  JOB VARCHAR2(9),
                  SAL NUMBER(7, 2),
                  DEPTNO NUMBER(2));

INSERT INTO EMP VALUES (1, 'SMITH', 'CLERK',     800,    20);
INSERT INTO EMP VALUES (2, 'ALLEN', 'SALESMAN', 1600,    30);
INSERT INTO EMP VALUES (3, 'WARD',  'SALESMAN', 1250,    30);
INSERT INTO EMP VALUES (4, 'JONES', 'MANAGER',  2975,    20);
INSERT INTO EMP VALUES (5, 'MARTIN','SALESMAN', 1250,    30);
INSERT INTO EMP VALUES (6, 'BLAKE', 'MANAGER',  2850,    30);
INSERT INTO EMP VALUES (7, 'CLARK', 'MANAGER',  2850,    10);
INSERT INTO EMP VALUES (8, 'SCOTT', 'ANALYST',  3000,    20);
INSERT INTO EMP VALUES (9, 'KING',  'PRESIDENT',3000,    10);
INSERT INTO EMP VALUES (10,'TURNER','SALESMAN', 1500,    30);
INSERT INTO EMP VALUES (11,'ADAMS', 'CLERK',    1500,    20);


SQL> SELECT deptno, job, SUM(sal)
  2  FROM emp
  3  GROUP BY deptno, ROLLUP(deptno, job);

    DEPTNO JOB         SUM(SAL)
---------- --------- ----------
        10 MANAGER         2850
        10 PRESIDENT       3000
        20 CLERK           2300
        20 ANALYST         3000
        20 MANAGER         2975
        30 MANAGER         2850
        30 SALESMAN        5600
        10                 5850
        20                 8275
        30                 8450
        10                 5850
        20                 8275
        30                 8450

13 rows selected.

SQL>
Home »
Oracle »
Analytical Functions » 

ROLLUP:
  1. ROLLUP
  2. Using Aggregate Functions with ROLLUP
  3. Using a Column Multiple Times in a ROLLUP Clause
Related: