GROUP BY는 그룸함수를 테이블보다 작은 그룹으로 나누어 사용할 수 있는 절이다.
테이블을 하나의 그룹으로 다루면 필요하지 않으나 테이블 안에서 그룹을 나눌 때 사용한다는 말이다.
1. 단일 그룹으로 사용.
SELECT절에 그룹 함수와 Column이 같이 기술되면 반드시 GROUP BY절이 기술 되어야 한다.
예를 들어
SELECT deptno, COUNT(*), AVG(sal), MIN(sal), MAX(sal), SUM(sal) FROM emp;
위와 같은 명령을 내리면 deptno는 다수의 값이지만, 나머지들은 하나의 값을 출력해야한다.
이런 경우는 출력되지 않고 에러가 난다. 출력하는 Column의 개수는 동일해야 겠다.
이럴 때 같은 deptno끼리 묶고, 각 deptno에 해당하는 sal의 COUNT, AVG, MIN, MAX, SUM값을
구할 수 있겠다.
SELECT deptno, COUNT(*), AVG(sal), MIN(sal), MAX(sal), SUM(sal)
FROM emp
GROUP BY deptno;
DEPTNO COUNT(*) AVG(SAL) MIN(SAL) MAX(SAL) SUM(SAL)
------- ---------- ---------- ---------- ---------- ----------
30 7 2128.57143 950 5500 14900
20 6 2475 800 4500 12375
10 4 2987.5 1500 5000 11950
2. 다수의 Column으로 그룹화
다수의 Column을 그룹화해서 표현할 수 있다.
예를 들어 부서별, 업무별로 그룹화해서 각각의 합계, 개수 등을 구할 수 있다.
SELECT deptno, job, COUNT(*), AVG(sal), SUM(sal)
FROM emp
GROUP BY deptno, job;
DEPTNO JOB COUNT(*) AVG(SAL) SUM(SAL)
------ ------------------ ---------- ---------- ----------
20 CLERK 2 950 1900
30 SALESMAN 4 1400 5600
20 MANAGER 1 2975 2975
30 CLERK 1 950 950
30 2 5250 10500
10 PRESIDENT 1 5000 5000
30 MANAGER 1 2850 2850
10 CLERK 1 1500 1500
20 1
10 MANAGER 1 2450 2450
20 ANALYST 2 3750 7500
DEPTNO JOB COUNT(*) AVG(SAL) SUM(SAL)
------ ------------------ ---------- ---------- ----------
10 1 3000 3000
3. ROLLUP과 CUBE 연산자를 이용한 GROUP BY
테이블은 행과 열로 이루어져 있는데 ROLLUP은 테이블 행의 합계를 구해주고,
CUBE는 행과 열의 부분합과 총계를 구해준다고 이해하면 되겠다.
예를 들어 부서별 합계는
SELECT deptno, sum(sal)
FROM emp
GROUP BY deptno;
DEPTNO SUM(SAL)
---------- ----------
30 19900
20 12375
10 11950
위와 같이 구할 수 있다.
여기서 부서 급여를 총합산한 금액을 구하려면 ROLLUP기능을 사용하면 된다.
SELECT deptno, sum(sal)
FROM emp
GROUP BY ROLLUP(deptno);
DEPTNO SUM(SAL)
---------- ----------
10 11950
20 12375
30 19900
44225
위와 같이 ROLLUP 기능을 통해 부서별 급여의 총합계가 구해졌다.
그렇다면 부서별, 업무별 급여의 총 합계를 구해보자.
SELECT deptno, job, sum(sal)
FROM emp
GROUP BY ROLLUP(deptno, job);
DEPTNO JOB SUM(SAL)
---------- ------------------ ----------
10 3000
10 CLERK 1500
10 MANAGER 2450
10 PRESIDENT 5000
10 11950
20
20 CLERK 1900
20 ANALYST 7500
20 MANAGER 2975
20 12375
30 10500
DEPTNO JOB SUM(SAL)
---------- ------------------ ----------
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
30 19900
44225
부서별, 업무별 합계를 구하기 위해 ROLLUP으로 deptno, job을 입력해줬으나,
부서별 합계만 구해진다.
부서별, 업무별 급여를 함께 구하려면 CUBE를 쓰면 된다.
SELECT deptno, job, sum(sal)
FROM emp
GROUP BY CUBE(deptno, job);
DEPTNO JOB SUM(SAL)
---------- ------------------ ----------
10500
44225
3000
CLERK 4350
ANALYST 7500
MANAGER 8275
SALESMAN 5600
PRESIDENT 5000
10 11950
10 3000
10 CLERK 1500
DEPTNO JOB SUM(SAL)
---------- ------------------ ----------
10 MANAGER 2450
10 PRESIDENT 5000
20
20 12375
20 CLERK 1900
20 ANALYST 7500
20 MANAGER 2975
30 10500
30 19900
30 CLERK 950
30 MANAGER 2850
DEPTNO JOB SUM(SAL)
---------- ------------------ ----------
30 SALESMAN 5600
CUBE는 위와 같이 업무별 합계도 같이 구해진다.
즉, 테이블에 DEPTNO는 행, JOB은 열일 때 ROLLUP은 행의 합계만 구해지고,
CUBE는 행과 열의 부분합, 총계가 구해진다고 보면 되겠다.
ROLLUP과 CUBE가 잘 적용되었는지 확인하려면 GROUPING함수를 사용하면 된다.
GROUPING함수를 사용해서 0이 리턴되면 해당 컬럼이 ROLLUP이나 CUBE 연산에 사용되었음을
나타내고, 1이 리턴되면 사용되지 않았음을 나타낸다.
SELECT deptno, job, SUM(sal), GROUPING(deptno)
FROM emp
GROUP BY CUBE(deptno, job);
DEPTNO JOB SUM(SAL) GROUPING(DEPTNO)
---------- ------------------ ---------- ----------------
10500 1
44225 1
3000 1
CLERK 4350 1
ANALYST 7500 1
MANAGER 8275 1
SALESMAN 5600 1
PRESIDENT 5000 1
10 11950 0
10 3000 0
10 CLERK 1500 0
DEPTNO JOB SUM(SAL) GROUPING(DEPTNO)
---------- ------------------ ---------- ----------------
10 MANAGER 2450 0
10 PRESIDENT 5000 0
20 0
20 12375 0
20 CLERK 1900 0
20 ANALYST 7500 0
20 MANAGER 2975 0
30 10500 0
30 19900 0
30 CLERK 950 0
30 MANAGER 2850 0
DEPTNO JOB SUM(SAL) GROUPING(DEPTNO)
---------- ------------------ ---------- ----------------
30 SALESMAN 5600 0
위 결과를 보면 deptno의 합계는 잘 적용되었으나 job의 합계는 적용되지 않았음을 볼 수 있다.
배운대로라면 job의 합계는 적용되어야 정상인데...흠...뭔가 오류가 있는 모양인데...
1이 나오면 그룹화할 집합이 없다는 뜻이라는데...
CUBE의 정의는 행과 열의 합계와 부분합을 구해준다는 정의만 알고 일단 넘어가자.
4. GROUPING SETS
GROUP BY절의 확장된 형태로 하나의 쿼리문에서 원하는 그룹핑 조건을 여러개 기술할 수 있다.
GROUPING SETS 함수 사용이 불가능했던 이전 버전에서는 복잡한 UNION ALL연산자라는 것을
사용해서 길게 코딩했다고 하는데, 지금은 GROUPING SETS함수를 이용해서
간단하게 사용할 수 있다.
예를 들어 부서, 업무를 한 그룹으로 묶고 부서, 관리자를 한 그룹으로 묶은 후 이 두 그룹 급여의
총액을 구한다고 하자.
SELECT deptno, job, mgr, sum(sal)
FROM emp
GROUP BY GROUPING SETS((deptno, job), (deptno, mgr));
DEPTNO JOB MGR SUM(SAL)
---------- ------------------ ---------- ----------
20 CLERK 1900
30 SALESMAN 5600
20 MANAGER 2975
30 CLERK 950
30 10500
10 PRESIDENT 5000
30 MANAGER 2850
10 CLERK 1500
20
10 MANAGER 2450
20 ANALYST 7500
DEPTNO JOB MGR SUM(SAL)
---------- ------------------ ---------- ----------
10 3000
20 7839 2975
30 10500
10 7839 2450
30 7698 6550
20 7566 7500
10 7782 1500
20
20 7902 800
10 8000
30 7839 2850
DEPTNO JOB MGR SUM(SAL)
---------- ------------------ ---------- ----------
20 7788 1100
5. HAVING절
WHERE절을 이용하여 조회하고자 하는 레코드를 선별할 수 있으나,
그룹에 대한 조건은 HAVING절에서 기술한다.
예를 들어 아래와 같이 그룹에 대한 조건을 WHERE절을 쓰면 에러 난다.
SELECT deptno, COUNT(*), SUM(sal)
FROM emp
WHERE COUNT(*) > 4
GROUP BY deptno;
WHERE COUNT(*) > 4
*
3행에 오류:
ORA-00934: 그룹 함수는 허가되지 않습니다
위처럼 에러가 발생하니 HAVING을 이용해 아래와 같이 코딩하면 정상적으로 결과가 출력된다.
SELECT deptno, COUNT(*), SUM(sal)
FROM emp
GROUP BY deptno
HAVING COUNT(*) > 4;
DEPTNO COUNT(*) SUM(SAL)
------ ---------- ----------
30 8 19900
20 6 12375
문제1> EMP 테이블에서 급여가 최대 5000 이상인 직원이 있는 부서에 대해서
부서번호, 평균 급여, 급여의 합을 구하여 출력하여라.
SELECT deptno, AVG(sal), SUM(sal)
FROM emp
GROUP BY deptno
HAVING MAX(sal) >= 5000
DEPTNO AVG(SAL) SUM(SAL)
---------- ---------- ----------
30 2487.5 19900
10 2987.5 11950
문제2> EMP 테이블에서 업무별 급여의 평균이 3000 이상인 업무에 대해서
업무명, 평균 급여, 급여의 합을 구하여 출력하여라.
SELECT job, AVG(sal), SUM(sal)
FROM emp
GROUP BY job
HAVING AVG(sal) > 3000;
JOB AVG(SAL) SUM(SAL)
------------------ ---------- ----------
5250 10500
PRESIDENT 5000 5000
ANALYST 3750 7500
문제3> EMP 테이블에서 전체 월급이 5000을 초과하는 각 업무에 대해서 업무와 월급여 합계를
출력하여라. 단 판매원은 제외하고 월 급여 합계로 내림차순 정렬하여라.
SELECT job, SUM(sal)
FROM emp
WHERE job NOT LIKE 'SALE%'
GROUP BY job
HAVING SUM(sal) > 5000
ORDER BY SUM(sal) DESC;
JOB SUM(SAL)
------------------ ----------
MANAGER 8275
ANALYST 7500
