SQL - scott(개념 + 실습)11

group by 

• 결과값을 원하는 열로 묶어서 출력하고 싶을 때 사용
• 구문이 길어지면 순서가 중요해짐. 어떤 거 부터 실행이 되느냐
• 엑셀에서 부분합과 같음

group by ~ having : 그룹을 잡을 때 조건을 주는 경우

 

1) 부서별 평균 급여 출력

SELECT
    deptno,
    AVG(sal)  -- ORA-00937: not a single-group group function
FROM
    emp;

ORA-00937: not a single-group group function ==> 오류 메세지

오류 해결) 부서별 평균 급여 출력

SELECT
    deptno,
    AVG(sal)
FROM
    emp
GROUP BY
    deptno;

 

2) 부서별, 직책별 급여 평균 출력

SELECT
    deptno,
    job,
    AVG(sal)
FROM
    emp
GROUP BY
    deptno,
    job
ORDER BY
    deptno,
    job;

ORDER BY : 생략 가능

 

3) group by 오류

SELECT
    ename,
    deptno,
    AVG(sal)
FROM
    emp
GROUP BY
    deptno;

group by : select 절에 group by 절에 포함안되는 열을 사용 불가

 

※ group by ~ having

4) 각 부서의 직책별 평균 급여를 구하되, 평균 급여가 2000이상인 그룹만 출력

SELECT
    deptno,
    job,
    AVG(sal)
FROM
    emp
GROUP BY
    deptno,
    job
HAVING
    AVG(sal) >= 2000;

 

 

※ where, having 차이점

• where : 출력 대상 행을 제한
• having : 그룹화된 대상을 제한

 

5) emp 테이블의 부서별 직책의 평균급여가 500이상인, 사원들의 부서번호, 직책, 평균 급여 출력

having 사용

SELECT
    deptno,
    job,
    AVG(sal)
FROM
    emp
GROUP BY
    deptno,
    job
HAVING
    AVG(sal) >= 500
ORDER BY
    deptno,
    job;

 

5-1) emp 테이블의 부서별 직책의 평균급여가 500이상인, 사원들의 부서번호, 직책, 평균 급여 출력

where 사용

SELECT
    deptno,
    job,
    AVG(sal)
FROM
    emp
WHERE
    AVG(sal) >= 500 -- group function is not allowed here
GROUP BY
    deptno,
    job;

 

6) 실행 순서 알아보기(where, having사용)

실행순서
SELECT         -----------------------  ⑤
    deptno,
    job,
    AVG(sal)
FROM           ---------------  ① 
    emp
WHERE          ---------------  ② 
    sal <= 3000
GROUP BY       ---------------  ③
    deptno,
    job
HAVING         ---------------  ④
    AVG(sal) >= 2000
ORDER BY       ----------------------  ⑥
    deptno,
    job;

from > where > group by > having by > select > order by 순

 

실습)  emp 테이블을 이용하여 부서번호(deptno), 평균급여(avg_sal), 최고 급여(max_sal), 최저급여(min_sal), 사원수(cnt)를 출력(단, 평균 급여를 출력할 때 소수점을 제외하고 각 부서번호별 출력)

SELECT
    deptno,
    round(AVG(sal)) AS avg_sal,
    MAX(sal)        AS max_sal,
    MIN(sal)        AS min_sal,
    COUNT(empno)    AS cnt
FROM
    emp
GROUP BY
    deptno;

 

실습) 같은 직책에 종사하는 사원이 3명 이상인 직책과 인원수를 출력

SELECT
    job,
    COUNT(*)
FROM
    emp
GROUP BY
    job
HAVING
    COUNT(job) >= 3;

 

실습) 사원들의 입사연도(hire_year)를 기준으로 부서별로 몇명이 입사했는지 출력

SELECT
    to_char(hiredate, 'YYYY') AS hire_year,
    deptno,
    COUNT(*)                  AS cnt
FROM
    emp
GROUP BY
    to_char(hiredate, 'YYYY'),
    deptno;