11.GROUP BY와 집계함수

-- ============================================================ -- 【시험용 예제】 EMP 의 커미션(COMM) 으로 5대 함수를 모두 적용 ★★ -- COMM 은 14명 중 4명만 값이 있고, 나머지 10명은 NULL 이다. -- ============================================================ SELECT COUNT(*) AS "행 개수", -- 전체 행 수 COUNT(COMM) AS "COMM 개수", -- COMM 이 NULL이 아닌 행 수 SUM(COMM) AS "COMM 합계", AVG(COMM) AS "COMM 평균", MAX(COMM) AS "COMM 최대", MIN(COMM) AS "COMM 최소" FROM EMP; -- 행 개수 COMM 개수 COMM 합계 COMM 평균 COMM 최대 COMM 최소 -- ------- --------- --------- --------- --------- --------- -- 14 4 2200 550 1400 0 -- ↑ ↑ -- NULL 10건은 세지 않음 2200 ÷ 4 = 550 (NULL 제외!) -- -- ⭐ 핵심 : SUM, AVG, MAX, MIN, COUNT(컬럼) 은 모두 NULL 을 '무시' 한다. -- 유일한 예외가 COUNT(*) 이다 (다음 절).

-- ============================================================ -- 【시험용 예제】 COUNT 세 가지의 결과가 모두 다르다 ★★★ -- ============================================================ SELECT COUNT(*) AS "전체 행", -- 14 COUNT(COMM) AS "COMM 있는 행", -- 4 (NULL 제외) COUNT(DISTINCT DEPTNO) AS "부서 종류", -- 3 (10,20,30) COUNT(DISTINCT JOB) AS "직무 종류" -- 5 FROM EMP; -- 전체 행 COMM 있는 행 부서 종류 직무 종류 -- ------- ------------ --------- --------- -- 14 4 3 5 -- -- ⭐ 정리 -- COUNT(*) → 무조건 전체 행 (NULL 신경 안 씀) -- COUNT(COMM) → COMM 이 있는 행만 = 14 - (NULL 10건) = 4 -- COUNT(DISTINCT DEPTNO)→ 서로 다른 부서번호의 개수 (40번은 사원이 없어 안 나옴)

-- ============================================================ -- 【시험용 예제】 같은 데이터, 다른 분모 → 다른 답 ★★★ -- ============================================================ SELECT AVG(COMM) AS "AVG(COMM)", -- NULL 제외 → 분모 4 AVG(NVL(COMM, 0)) AS "AVG(NVL(COMM,0))", -- NULL을 0으로 → 분모 14 SUM(COMM) AS "SUM", -- 2200 (둘 다 동일) SUM(COMM) / COUNT(*) AS "SUM/COUNT(*)", -- 2200 ÷ 14 SUM(COMM) / COUNT(COMM) AS "SUM/COUNT(COMM)" -- 2200 ÷ 4 FROM EMP; -- AVG(COMM) AVG(NVL(COMM,0)) SUM SUM/COUNT(*) SUM/COUNT(COMM) -- --------- ---------------- ---- ------------ --------------- -- 550 157 2200 157 550 -- -- ⭐ 결론 -- AVG(COMM) = SUM(COMM) / COUNT(COMM) ← 분모 4 -- AVG(NVL(COMM,0)) = SUM(COMM) / COUNT(*) ← 분모 14 -- 분자(SUM)는 같지만 분모가 다르다!

-- ============================================================ -- 【이해용 예제】 부서별 인원수와 급여 합계 -- ============================================================ SELECT DEPTNO AS 부서번호, COUNT(*) AS 인원수, -- 그룹마다 행 수를 센다 SUM(SAL) AS 급여합계, ROUND(AVG(SAL)) AS 평균급여 FROM EMP GROUP BY DEPTNO -- DEPTNO 값이 같은 행끼리 묶는다 ORDER BY DEPTNO; -- 부서번호 인원수 급여합계 평균급여 -- -------- ------ -------- -------- -- 10 3 8750 2917 -- 20 5 10875 2175 -- 30 6 9400 1567 -- -- ⭐ GROUP BY DEPTNO → 부서 3개 그룹 → 결과 3행 (14행이 3행으로 압축됨)

-- ============================================================ -- 【시험용 예제】 그룹 컬럼이 아닌 일반 컬럼은 SELECT 에 못 온다 ★★ -- ============================================================ -- ❌ 오류: ENAME 은 GROUP BY 에도 없고 집계함수도 아니다 SELECT DEPTNO, ENAME, SUM(SAL) FROM EMP GROUP BY DEPTNO; -- ORA-00979: not a GROUP BY expression -- → 한 부서에 사원이 여러 명인데 ENAME 을 무엇으로 보여줘야 할지 알 수 없다! -- (10번 그룹의 ENAME 은 CLARK? KING? MILLER? → 결정 불가) -- ✅ 해결 ① ENAME 을 빼거나 SELECT DEPTNO, SUM(SAL) FROM EMP GROUP BY DEPTNO; -- ✅ 해결 ② ENAME 도 GROUP BY 에 넣는다 (그러면 그룹 기준이 달라진다) SELECT DEPTNO, ENAME, SAL FROM EMP GROUP BY DEPTNO, ENAME, SAL;

-- SELECT 에 DEPTNO 가 없어도 GROUP BY 는 정상 동작한다 (반대는 불가) SELECT SUM(SAL) AS 급여합계 FROM EMP GROUP BY DEPTNO; -- 부서별로 묶지만, 어느 부서인지는 표시하지 않음 -- 급여합계 -- -------- -- 8750 -- 10875 -- 9400 → 3행 (어느 부서인지 알 수 없어 실용성은 낮다)

-- ============================================================ -- 【시험용 예제】 "급여합계가 9000 초과인 부서" 를 찾을 때 ★★★ -- ============================================================ -- ❌ WHERE 에 집계함수를 쓸 수 없다 SELECT DEPTNO, SUM(SAL) FROM EMP WHERE SUM(SAL) > 9000 -- ORA-00934: group function is not allowed here GROUP BY DEPTNO; -- → WHERE 는 '그룹으로 묶기 전' 에 실행되므로, 아직 SUM(SAL) 이 존재하지 않는다! -- ✅ 그룹에 대한 조건은 HAVING 을 쓴다 SELECT DEPTNO, SUM(SAL) AS 급여합계 FROM EMP GROUP BY DEPTNO HAVING SUM(SAL) > 9000; -- 그룹핑 후, 합계로 그룹을 거른다 -- DEPTNO 급여합계 -- ------ -------- -- 20 10875 -- 30 9400 → 10번(8750)은 9000 이하라 제외됨

-- ============================================================ -- 【시험용 예제】 WHERE 와 HAVING 을 함께 쓰기 ★★ -- "급여 1000 이상인 사원들만 대상으로, 부서별 인원이 3명 이상인 부서" -- ============================================================ SELECT DEPTNO, COUNT(*) AS 인원수, SUM(SAL) AS 급여합계 FROM EMP WHERE SAL >= 1000 -- ① 먼저 급여 1000 미만인 개별 행을 제거 (SMITH 800, JAMES 950 탈락) GROUP BY DEPTNO -- ② 남은 행을 부서별로 묶고 HAVING COUNT(*) >= 3 -- ③ 인원이 3명 이상인 그룹만 남긴다 ORDER BY DEPTNO; -- DEPTNO 인원수 급여합계 -- ------ ------ -------- -- 10 3 8750 ← 3명 모두 1000 이상이라 그대로 통과 -- 20 4 10075 ← SMITH(800) 제외되어 5명→4명 -- 30 5 8450 ← JAMES(950) 제외되어 6명→5명 -- -- ⭐ 순서가 핵심 -- WHERE 로 '행' 을 먼저 거르고 → GROUP BY 로 묶고 → HAVING 으로 '그룹' 을 거른다. -- 같은 조건이라도 WHERE 에 쓸지 HAVING 에 쓸지에 따라 결과가 달라질 수 있다.

-- ============================================================ -- 【이해용 예제】 부서별 · 직무별 인원과 급여합계 -- ============================================================ SELECT DEPTNO AS 부서, JOB AS 직무, COUNT(*) AS 인원수, SUM(SAL) AS 급여합계 FROM EMP GROUP BY DEPTNO, JOB -- (부서, 직무) 조합마다 하나의 그룹 ORDER BY DEPTNO, JOB; -- 부서 직무 인원수 급여합계 -- ---- --------- ------ -------- -- 10 CLERK 1 1300 -- 10 MANAGER 1 2450 -- 10 PRESIDENT 1 5000 -- 20 ANALYST 2 6000 -- 20 CLERK 2 1900 -- 20 MANAGER 1 2975 -- 30 CLERK 1 950 -- 30 MANAGER 1 2850 -- 30 SALESMAN 4 5600 -- → 9개 그룹 -- -- ⭐ 07강에서 배운 DISTINCT DEPTNO, JOB 이 9건이었던 것과 같은 원리다. -- (실제 존재하는 조합의 수만큼 그룹이 생긴다)

-- ============================================================ -- 【시험용 예제】 GROUP BY 는 NULL 을 하나의 그룹으로 묶는다 ★ -- ============================================================ SELECT COMM AS 커미션, COUNT(*) AS 인원수 FROM EMP GROUP BY COMM ORDER BY COMM; -- 커미션 인원수 -- ------ ------ -- 0 1 -- 300 1 -- 500 1 -- 1400 1 -- (NULL) 10 ← NULL 인 10명이 '하나의 그룹' 으로 묶인다 ⭐ -- -- ⭐ 06강 복습 : NULL 끼리는 비교 불가(UNKNOWN)지만, -- GROUP BY / DISTINCT 는 예외적으로 NULL 을 '같은 것' 으로 묶는다.

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

SELECT DEPTNO, SUM(SAL) FROM EMP WHERE SUM(SAL) > 9000 GROUP BY DEPTNO;

-- (A) SELECT DEPTNO, COUNT(*) FROM EMP GROUP BY DEPTNO; -- (B) SELECT DEPTNO, COUNT(*) FROM EMP WHERE SAL >= 1000 GROUP BY DEPTNO;