[ORACLE] 부서별 컬럼을 만들고, 직원 수를 나타내기(GROUP BY or 조건문 활용)

문제

다음과 같은 결과를 생성하는 SQL 작성

10번부서	20번부서	30번부서
3	5	6

 

정답1

SELECT SUM(DECODE(DEPTNO, 10, COUNT(DEPTNO), 0)) AS "10번부서",
       SUM(DECODE(DEPTNO, 20, COUNT(DEPTNO), 0)) AS "20번부서",
       SUM(DECODE(DEPTNO, 30, COUNT(DEPTNO), 0)) AS "30번부서"
FROM EMP
GROUP BY DEPTNO;

정답2

SELECT COUNT(CASE WHEN DEPTNO = 10 THEN 1 END) AS "10번부서",
       COUNT(CASE WHEN DEPTNO = 20 THEN 1 END) AS "20번부서",
       COUNT(CASE WHEN DEPTNO = 30 THEN 1 END) AS "30번부서"
FROM EMP;

정답3

SELECT SUBQUERY1.NUM AS "10번부서", SUBQUERY2.NUM AS "20번부서", SUBQUERY3.NUM AS "30번부서"
FROM (SELECT DEPTNO, COUNT(*) AS NUM
      FROM EMP
      GROUP BY DEPTNO
      HAVING DEPTNO = 10) SUBQUERY1,
      
     (SELECT DEPTNO, COUNT(*) AS NUM
      FROM EMP
      GROUP BY DEPTNO
      HAVING DEPTNO = 20) SUBQUERY2,
      
     (SELECT DEPTNO, COUNT(*) AS NUM
      FROM EMP
      GROUP BY DEPTNO
      HAVING DEPTNO = 30) SUBQUERY3;

- FROM 절 SUBQUERY를 인라인뷰(inline views)라고 한다.

- 한글 ALIAS : 더블 쿼테이션(" ")을 사용한다.

- HAVING : GROUP BY 된 결과에 대한 필터