CUBE returns rows containing a subtotal for all combinations of columns and
a row containing the grand total.
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>
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>