GROUP BY와 집계 함수

GROUP BY는 같은 값을 가진 행을 한 그룹으로 묶습니다. 집계 함수는 그룹마다 값을 하나씩 냅니다.

"부서마다 사원이 몇 명인가?"는 행 하나가 아니라 여러 행을 묶어야 답할 수 있는 질문입니다. 행을 묶는 일은 GROUP BY 절이 맡고, 묶인 행들을 개수·합계·평균 같은 값 하나로 줄이는 일은 집계 함수(aggregate function)가 맡습니다.

SELECT DEPTNO,  COUNT(*),  COUNT(DEPTNO),  SUM(SAL),  AVG(SAL)FROM EMPGROUP BY DEPTNOORDER BY DEPTNO;

1같은 값끼리 묶기와 NULL 그룹

GROUP BY DEPTNO는 DEPTNO 값이 같은 행을 한 그룹에 넣습니다. 10번과 30번에는 1행씩, 20번에는 3행이 들어갑니다. 정하늘처럼 DEPTNO가 NULL인 행도 빠지지 않고, NULL끼리 모여 그룹 하나를 따로 이룹니다. 그래서 EMP는 그룹이 4개입니다. 조건식에서는 NULL = NULL이 참이 아니지만, 묶을 때는 NULL끼리 같은 그룹으로 칩니다. GROUP BY를 쓰지 않으면 테이블 전체가 한 그룹입니다.

2집계 함수와 NULL

집계 함수는 그룹 안의 값을 모아 하나로 줄입니다. COUNT(*)만 행을 세고, 나머지는 NULL인 칸을 건너뜁니다.

MAX·MIN도 NULL을 건너뜁니다. COUNT(DISTINCT 열)은 NULL을 뺀 뒤 같은 값을 한 번만 셉니다. 걸러서 남은 행이 하나도 없어도 GROUP BY가 없으면 그 한 그룹이 남아 1행이 나옵니다. 이때 COUNT(*)는 0, SUM·AVG는 NULL입니다. GROUP BY가 있으면 묶을 행이 없어 0행입니다.

3SELECT에 올 수 있는 것

그룹 하나가 결과 한 줄이 되므로, SELECT에는 그룹마다 값이 하나로 정해지는 것만 올 수 있습니다. GROUP BY에 쓴 열, 집계 함수, 상수와 이것들로 만든 식입니다. 20번 그룹은 ENAME이 셋이라, GROUP BY에 없는 ENAME을 그대로 쓰면 Oracle은 ORA-00979 오류를 냅니다. HAVING 절(묶은 그룹을 조건으로 거르는 절 — HAVING 절 레슨에서 자세히)과 ORDER BY에도 같은 제한이 걸립니다. GROUP BY 없이 집계 함수와 일반 열을 함께 써도 한 줄에 담을 수 없어 ORA-00937 오류입니다.

SELECT ENAME,
  COUNT(*)
FROM EMP;

예제

그림의 쿼리입니다. 결과를 DEPTNO 순서로 보려고 ORDER BY DEPTNO를 붙였습니다.

SELECT DEPTNO,
  COUNT(*),
  COUNT(DEPTNO),
  SUM(SAL),
  AVG(SAL)
FROM EMP
GROUP BY DEPTNO
ORDER BY DEPTNO;

NULL 그룹이 맨 아래인 것은 Oracle이 오름차순(작은 값부터 세우는 정렬)에서 NULL을 가장 큰 값으로 보기 때문입니다. SQL Server는 맨 위에 둡니다(ORDER BY 절에서 자세히).

GROUP BY 없이 집계하면 EMP 전체가 한 줄로 줄어듭니다.

SELECT COUNT(*),
  COUNT(DEPTNO)
FROM EMP;

시험 포인트

• COUNT(*)는 NULL과 상관없이 행을 세고, COUNT(열)은 그 열이 NULL인 행을 빼고 셉니다.
• SUM·AVG·MAX·MIN도 NULL을 빼고 계산합니다. AVG(열)은 NULL이 아닌 값들만의 평균입니다.
• COUNT(DISTINCT 열)은 NULL을 빼고, 같은 값은 한 번만 셉니다.
• GROUP BY 열이 NULL인 행들은 NULL 그룹 하나로 모입니다.
• 남은 행이 없으면 GROUP BY가 없을 때는 COUNT(*) 0 · SUM NULL 한 줄, 있을 때는 0행입니다.
• GROUP BY를 쓴 쿼리의 SELECT·HAVING·ORDER BY에는 GROUP BY 열, 집계 함수, 상수와 그 식만 옵니다(ORA-00979).

확인 문제

1. 다음 쿼리의 결과는 몇 줄일까요? NULL 그룹이 있을까요?

SELECT DEPTNO,
  COUNT(*)
FROM EMP
WHERE SAL >= 400
GROUP BY DEPTNO
ORDER BY DEPTNO;

2. 다음 쿼리의 결과 세 값은 무엇일까요?

SELECT COUNT(*),
  COUNT(DEPTNO),
  COUNT(DISTINCT DEPTNO)
FROM EMP;

3. 다음 쿼리는 ENAME 때문에 Oracle에서 오류가 납니다. GROUP BY 줄을 GROUP BY DEPTNO, ENAME⁠으로 바꾸면 결과는 몇 줄이고, 부서별 월급 합계라는 처음 뜻과 무엇이 달라질까요?

SELECT DEPTNO,
  ENAME,
  SUM(SAL)
FROM EMP
GROUP BY DEPTNO;

SELECT DEPTNO,
  ENAME,
  SUM(SAL)
FROM EMP
GROUP BY DEPTNO, ENAME
ORDER BY DEPTNO, ENAME;