Oracle Analytic Function - Oracle/PLSQL CUBE Function






CUBE returns rows containing a subtotal for all combinations of columns and a row containing the grand total.

Example


CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
                  ENAME VARCHAR2(10),
                  JOB VARCHAR2(9),
                  SAL NUMBER(7, 2),
                  DEPTNO NUMBER(2));
-- from w  w  w  .j  a  va 2 s .  c o m
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>
SQL> SELECT deptno, job, SUM(sal)
  2  FROM emp
  3  GROUP BY CUBE(deptno, job)
  4  ORDER BY deptno, job;

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

    DEPTNO JOB         SUM(SAL)
---------- --------- ----------
           CLERK           2300
           MANAGER         8675
           PRESIDENT       3000
           SALESMAN        5600
                          22575

16 rows selected.

SQL>

Switches deptno and job:


SQL> SELECT job, deptno, SUM(sal)
  2  FROM emp-- from   ww  w  .  j av a 2 s.  c o  m
  3  GROUP BY CUBE(job, deptno)
  4  ORDER BY job, deptno;

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

JOB           DEPTNO   SUM(SAL)
--------- ---------- ----------
SALESMAN                   5600
                  10       5850
                  20       8275
                  30       8450
                          22575

16 rows selected.

SQL>




CUBE and DENSE_RANK

The following query uses CUBE and DENSE_RANK() to get all rankings of salaries by department and job:


CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
                  ENAME VARCHAR2(10),
                  JOB VARCHAR2(9),
                  SAL NUMBER(7, 2),
                  DEPTNO NUMBER(2));
-- from  w w  w .  j  a  v  a  2s  .  c o m
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  DENSE_RANK() OVER (ORDER BY SUM(sal) DESC) AS rank
  3  FROM emp
  4  GROUP BY CUBE(deptno, job)
  5  ORDER BY deptno, job;

    DEPTNO JOB         SUM(SAL)       RANK
---------- --------- ---------- ----------
        10 MANAGER         2850          9
        10 PRESIDENT       3000          7
        10                 5850          5
        20 ANALYST         3000          7
        20 CLERK           2300         10
        20 MANAGER         2975          8
        20                 8275          4
        30 MANAGER         2850          9
        30 SALESMAN        5600          6
        30                 8450          3
           ANALYST         3000          7

    DEPTNO JOB         SUM(SAL)       RANK
---------- --------- ---------- ----------
           CLERK           2300         10
           MANAGER         8675          2
           PRESIDENT       3000          7
           SALESMAN        5600          6
                          22575          1

16 rows selected.

SQL>