# Composite Columns and Concatenated Groupings and Practice

composite columns would mean skipping aggregation across certain levels.
That is, GROUP BY ROLLUP(a, (b, c))
is equivalent to
GROUP BY a, b, c UNION ALL
GROUP BY a UNION ALL
GROUP BY ()

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

DEPTNO JOB SAL SUM(SAL)
---------- --------- ---------- ----------
10 CLERK 1300 1300
10 MANAGER 2450 2450
10 PRESIDENT 5000 5000
10 8750
20 CLERK 800 800
20 CLERK 1100 1100
20 ANALYST 3000 6000
20 MANAGER 2410 2410
20 MANAGER 2975 2975
20 13285
30 CLERK 950 950

DEPTNO JOB SAL SUM(SAL)
---------- --------- ---------- ----------
30 MANAGER 2850 2850
30 SALESMAN 1500 1500
30 SALESMAN 1600 1600
30 SALESMAN 1250 2500
30 9400
31435

The example in the slide can also be written as:
SELECT department_id, job_id, manager_id, SUM(salary)
FROM employees
GROUP BY department_id,job_id, manager_id
UNION ALL
SELECT department_id, TO_CHAR(NULL),TO_NUMBER(NULL), SUM(salary)
FROM employees
GROUP BY department_id
UNION ALL

SQL> select deptno,job,empno,sum(sal) from emp
2 group by grouping sets((deptno,empno,job),(deptno,job),(empno,job));

DEPTNO JOB EMPNO SUM(SAL)
---------- --------- ---------- ----------
20 MANAGER 7400 2410
30 CLERK 7900 950
20 CLERK 7369 800
30 SALESMAN 7499 1600
30 SALESMAN 7521 1250
20 MANAGER 7566 2975
30 SALESMAN 7654 1250
30 MANAGER 7698 2850
10 MANAGER 7782 2450
20 ANALYST 7788 3000
10 PRESIDENT 7839 5000

DEPTNO JOB EMPNO SUM(SAL)
---------- --------- ---------- ----------
30 SALESMAN 7844 1500
20 CLERK 7876 1100
20 ANALYST 7902 3000
10 CLERK 7934 1300
MANAGER 7400 2410
CLERK 7900 950
CLERK 7369 800
SALESMAN 7499 1600
SALESMAN 7521 1250
MANAGER 7566 2975
SALESMAN 7654 1250

DEPTNO JOB EMPNO SUM(SAL)
---------- --------- ---------- ----------
MANAGER 7698 2850
MANAGER 7782 2450
ANALYST 7788 3000
PRESIDENT 7839 5000
SALESMAN 7844 1500
CLERK 7876 1100
ANALYST 7902 3000
CLERK 7934 1300
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000

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

SELECT TO_NUMBER(NULL), TO_CHAR(NULL),TO_NUMBER(NULL), SUM(sala ry)
FROM employees
GROUP BY ();

SQL> select deptno,job,sal,sum(sal) from emp
2 group by deptno,rollup(job),cube(sal);

DEPTNO JOB SAL SUM(SAL)
---------- --------- ---------- ----------
10 CLERK 1300 1300
10 PRESIDENT 5000 5000
10 MANAGER 2450 2450
20 CLERK 800 800
20 CLERK 1100 1100
20 ANALYST 3000 6000
20 MANAGER 2410 2410
20 MANAGER 2975 2975
30 SALESMAN 1500 1500
30 SALESMAN 1600 1600
30 CLERK 950 950

DEPTNO JOB SAL SUM(SAL)
---------- --------- ---------- ----------
30 SALESMAN 1250 2500
30 MANAGER 2850 2850
10 1300 1300
10 5000 5000
10 2450 2450
20 800 800
20 1100 1100
20 3000 6000
20 2410 2410
20 2975 2975
30 1500 1500

DEPTNO JOB SAL SUM(SAL)
---------- --------- ---------- ----------
30 1600 1600
30 950 950
30 1250 2500
30 2850 2850
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 8750
20 CLERK 1900
20 ANALYST 6000
20 MANAGER 5385

DEPTNO JOB SAL SUM(SAL)
---------- --------- ---------- ----------
20 13285
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
30 9400

• 博文量
36
• 访问量
28421