GROUPING() with CUBE

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
  2   CASE GROUPING(deptno)
  3  WHEN 1 THEN 'All departments'
  4  ELSE 'deptno'
  5   END AS div,
  6  CASE GROUPING(job)
  7  WHEN 1 THEN 'All jobs'
  8  ELSE 'job'
  9  END AS job,
 10  SUM(sal)
 11  FROM emp
 12  GROUP BY CUBE(deptno, job)
 13  ORDER BY deptno, job;

DIV             JOB        SUM(SAL)
--------------- -------- ----------
deptno          All jobs       5850
deptno          job            3000
deptno          job            2850
deptno          All jobs       8275
deptno          job            2975
deptno          job            3000
deptno          job            2300
deptno          All jobs       8450
deptno          job            2850
deptno          job            5600
All departments All jobs      22575
All departments job            8675
All departments job            3000
All departments job            2300
All departments job            5600
All departments job            3000

16 rows selected.

SQL>