[Oracle] GROUPING SETS

GROUPING SETS?

GROUP BY절에서 그룹 조건을 여러 개 지정할 수 있다.

각 그룹 조건에 대해 GROUP BY한 결과를 UNION ALL한 결과와 일치

GROUPING SETS절	GROUP BY절
GROUP BY GROUPING SETS(a, b, c)	GROUP BY a UNION ALL GROUP BY b UNION ALL GROUP BY c
GROUP BY GROUPING SETS(a, b, (b,c))	GROUP BY a UNION ALL GROUP BY b UNION ALL GROUP BY b, c
GROUP BY GROUPING SETS(a, ROLLUP(b,c))	GROUP BY a UNION ALL GROUP BY ROLLUP(b, c)
GROUP BY GROUPING SETS(a, CUBE(b,c))	GROUP BY a UNION ALL GROUP BY CUBE(b, c)

 

EX) "(각) 학과내에서" 학년별 인원수와 태어난 연도별 인원수를 출력

SELECT deptno, grade, TO_CHAR(birthdate, 'YYYY'), COUNT(*)
FROM student
GROUP BY GROUPING SETS((deptno, grade), (deptno, TO_CHAR(birthdate, 'YYYY')));