# Enhancements to the Group By clause:

The ROLLUP operator creates subtotals that roll up from the most detailed level to a grand total, following
the grouping list specified in the GROUP BY clause
The Rollup operation to produce cumulative aggregates such as subtotals:
SQL> select deptno,job,sum(sal) from emp
2 where deptno<40
3 group by deptno,job;

DEPTNO JOB SUM(SAL)
---------- --------- ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
20 CLERK 1900
20 ANALYST 6000
20 MANAGER 5385
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600

SQL> select deptno,job,sum(sal) from emp
2 where deptno<40
3 group by rollup(deptno,job);

DEPTNO JOB SUM(SAL)
---------- --------- ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 8750
20 CLERK 1900
20 ANALYST 6000
20 MANAGER 5385
20 13285
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600

DEPTNO JOB SUM(SAL)
---------- --------- ----------
30 9400
31435

Then it creates progressively higher-level subtotals, moving from right to left through the
list of grouping columns. (In the preceding example, the sum of salaries for each department is calculated,
followed by the sum of salaries for all departments.):

SQL> select deptno,job,sum(sal) from emp
2 where deptno<40
3 group by rollup(job,deptno);

DEPTNO JOB SUM(SAL)
---------- --------- ----------
10 CLERK 1300
20 CLERK 1900
30 CLERK 950
CLERK 4150
20 ANALYST 6000
ANALYST 6000
10 MANAGER 2450
20 MANAGER 5385
30 MANAGER 2850
MANAGER 10685
30 SALESMAN 5600

DEPTNO JOB SUM(SAL)
---------- --------- ----------
SALESMAN 5600
10 PRESIDENT 5000
PRESIDENT 5000
31435

While ROLLUP produces only a
fraction of possible subtotal combinations, CUBE produces subtotals for all possible combinations of
groupings specified in the GROUP BY clause, and a grand total.

SQL> select deptno,job,sum(sal) from emp
2 where deptno<40
3 group by cube(deptno,job);

DEPTNO JOB SUM(SAL)
---------- --------- ----------
31435
CLERK 4150
ANALYST 6000
MANAGER 10685
SALESMAN 5600
PRESIDENT 5000
10 8750
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
20 13285

DEPTNO JOB SUM(SAL)
---------- --------- ----------
20 CLERK 1900
20 ANALYST 6000
20 MANAGER 5385
30 9400
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600

sum(sal);最后按所有部门求total sum(sal)。
（CLERK 4150）this rows is cross-tabulation-rows.

• 博文量
36
• 访问量
28518