ROLLUP
ROLLUP returns a row containing a subtotal for each group of rows and
a row containing a grand total for all the groups.
Single Column and ROLLUP:
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, SUM(sal)
2 FROM emp
3 GROUP BY ROLLUP(deptno)
4 ORDER BY deptno;
DEPTNO SUM(SAL)
---------- ----------
10 5850
20 8275
30 8450
22575
SQL>
Multiple Columns and ROLLUP
SQL> SELECT deptno, job, SUM(sal)
2 FROM emp
3 GROUP BY ROLLUP(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
22575
11 rows selected.
SQL>
Changing the Position of Columns Passed to ROLLUP
SQL> SELECT job, deptno, SUM(sal)
2 FROM emp
3 GROUP BY ROLLUP(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
SALESMAN 5600
22575
13 rows selected.
SQL>