[SQL]그룹화 관련 함수 (ROLLUP,CUBE)

 /*그룹화와 관련된 여러함수 */
/*ROLLUP, CUBE*/
/*  
    SELECT [조회할 컬럼1],[컬럼2]..
    FROM [조회할 테이블 이름]
    WHERE [조회할 행을 선별하는 조건식]
    GROUP BY ROLLUP [그룹화 열 지정(여러개 가능)]
   
    GROUP BY CUBE [그룹화 열 지정(여러개 가능)]
    ->그룹화 데어티의 합계를 출력하는데 사용
*/

/*ROLLUP*/
SELECT DEPTNO,JOB,COUNT(*),MAX(SAL),ROUND(AVG(SAL),2)
FROM EMP
GROUP BY ROLLUP(DEPTNO,JOB)
ORDER BY DEPTNO,JOB;

/*CUBE*/
SELECT DEPTNO,JOB,COUNT(*),MAX(SAL),ROUND(AVG(SAL),2)
FROM EMP
GROUP BY CUBE(DEPTNO,JOB)
ORDER BY DEPTNO,JOB;

/*7-27*/
SELECT DEPTNO,JOB,COUNT(*),MAX(SAL),ROUND(AVG(SAL),2)
FROM EMP
GROUP BY DEPTNO, ROLLUP(JOB);

/*7-28 ROLLUP함수에 DEPTNO지정*/
SELECT DEPTNO,JOB,COUNT(*),MAX(SAL),ROUND(AVG(SAL),2)
FROM EMP
GROUP BY ROLLUP(DEPTNO), JOB;