[Oracle(SQL)] GROUP BY와 HAVING

ORDER BY

- SELECT한 컬럼에 대해 정렬을 할 때 작성하는 구문

- SELECT한 구문의 가장 마지막에 작성하며 실행순서도 가장 마지막에 수행됨

ORDERBY 컬럼명 | 별칭 | 컬럼순번 정렬방식 [NULLS FIRST(ASC,DESC) | LAST]

 

GROUP BY

- 같은 값들이 여러 개 기록된 컬럼을 가지고 같은 값들을 하나의 그룹으로 묶음

- 그룹으로 묶은 값에 대해서 SELECT 절에서 그룹함수를 사용한다

GROUPO BY 컬럼명 | 함수식, ...

 

HAVING

- 그룹함수로 구해올 그룹에 대해 조건을 설정할 때 사용

HAVING 컬럼명 | 함수식 비교연산자 비교값

 

서술순서(숫자는 연산순서)

 5 : SELECT 컬럼명 AS 별칭, 계산식, 함수식
 1 : FROM 참조할 테이블명
 2 : WHERE 컬럼명 | 함수식 비교연산자 비교값
 3 : GROUP BY 그룹을 묶을 컬럼명
 4 : HAVING 그룹함수식 비교연산자 비교값
 6 : ORDER BY 컬럼명 | 별칭 | 컬럼순번 정렬방식 [NULLS FIRST | LAST]

 

부서 기준으로 그루핑

SELECT
        COUNT(*)
      , DEPT_CODE
   FROM EMPLOYEE
  GROUP BY DEPT_CODE;

 

같은 부서이면서 같은 직급인 사람 기준으로 그루핑(부서 기준으로 정렬)

SELECT
        DEPT_CODE
      , JOB_CODE
      , SUM(SALARY)
      , COUNT(*)
   FROM EMPLOYEE
  GROUP BY DEPT_CODE
         , JOB_CODE
 ORDER BY 1;

 

직원 테이블에서 부서 코드별 그룹을 지정하여 부서코드, 그룹별 급여의 합계, 그룹별 급여의 평균(정수처리), 인원수 조회하고 부서코드 순으로 오름차순 정렬하세요

(ORDER BY를 1로 지정해줘도 같은값 나옴)

SELECT
        DEPT_CODE
      , SUM(SALARY)
      , FLOOR(AVG(SALARY))
      , COUNT(*)
   FROM EMPLOYEE
GROUP BY DEPT_CODE
ORDER BY DEPT_CODE;

 

직원 테이블에서 직급코드, 보너스를 받는 사원 수를 조회하여 직급코드 순으로 오름차순 정렬하세요

SELECT
        JOB_CODE
      , COUNT(BONUS)
   FROM EMPLOYEE
GROUP BY JOB_CODE
ORDER BY JOB_CODE;

 

직원 테이블에서 직급코드, 보너스를 받는 사원 수를 조회하여 직급코드 순으로 오름차순 정렬하세요
단, 보너스를 받는 사람이 없는 직급코드의 경우 RESULT SET에서 제외한다.

(COUNT 괄호 안에 * 전체로 조회해도 문제없이 조회된다.)

SELECT
        JOB_CODE
      , COUNT(BONUS)
   FROM EMPLOYEE
  WHERE BONUS IS NOT NULL
  GROUP BY JOB_CODE
  ORDER BY JOB_CODE;

 

직원 테이블에서 주민번호의 8번째 자리를 조회하여 1이면 남, 2이면 여로 결과 조회하고 성별별 급여 평균(정수처리), 급여 합계, 인원수를 조회한 뒤 인원수로 내림차순 정렬하세요.

(GROUP BY절에서는 별칭을 사용 못한다. 이유는 SELECT절이 GROUP BY 보다 늦게 수행되기 때문)

SELECT
        DECODE(SUBSTR(EMP_NO, 8, 1), 1, '남', 2, '여') 성별
      , FLOOR(AVG(SALARY)) 평균
      , SUM(SALARY) 합계
      , COUNT(*) 인원수
   FROM EMPLOYEE
GROUP BY DECODE(SUBSTR(EMP_NO, 8, 1), 1, '남', 2, '여')
ORDER BY 인원수 DESC;

 

300만원 이상의 월급을 받는 사람들을 대상으로 부서별 그룹 월급 평균 계산

SELECT
        DEPT_CODE
      , FLOOR(AVG(SALARY)) 평균
   FROM EMPLOYEE
  WHERE SALARY > 3000000
  GROUP BY DEPT_CODE
  ORDER BY 1;

 

모든 직원을 대상으로 부서별 월급 평균을 구한 뒤 평균이 300만원 이상인 부서 조회

SELECT
        DEPT_CODE
      , FLOOR(AVG(SALARY)) 평균
   FROM EMPLOYEE
  GROUP BY DEPT_CODE
HAVING FLOOR(AVG(SALARY)) > 3000000 -- 그루핑에 대한 조건을 추가해줄때, GROUP BY와 같이 움직임
  ORDER BY 1;

 

급여 합계가 가장 많은 부서의 부서코드와 급여 합계를 구하세요

(서브쿼리를 이용한 것)

SELECT
        DEPT_CODE
      , SUM(SALARY)
   FROM EMPLOYEE
 GROUP BY DEPT_CODE
HAVING SUM(SALARY) = (SELECT MAX(SUM(SALARY))
                        FROM EMPLOYEE
                       GROUP BY DEPT_CODE
                     );

 

집계함수

ROLLUP 함수

- 그룹별로 중간 집계 처리를 하는 함수
- GROUP BY 절에서만 사용하는 함수
- 그룹별로 묶여진 값에 대한 중간 집계와 총 집계를 구할 때 사용한다
- 그룹별로 계산된 결과값들에 대한 총 집계가 자동으로 추가됨

SELECT
       JOB_CODE
     , SUM(SALARY)
  FROM EMPLOYEE
 GROUP BY ROLLUP(JOB_CODE)
 ORDER BY 1;

 

CUBE

- 그룹별 산출한 결과를 집계하는 함수

(여기선 위와 값이 동일하게 나온다)

SELECT
       JOB_CODE
     , SUM(SALARY)
  FROM EMPLOYEE
 GROUP BY CUBE(JOB_CODE)
 ORDER BY 1;

 

인자로 전달한 그룹 중에서 가장 먼저 지정한 그룹별 합계와 총 합계를 구하는 함수

ROLLUP(중간중간 부서별마다 합계가 들어가 있다. 중간집계)

SELECT
       DEPT_CODE
     , JOB_CODE
     , SUM(SALARY)
  FROM EMPLOYEE
 GROUP BY ROLLUP(DEPT_CODE, JOB_CODE)
 ORDER BY 1;

그룹으로 지정 된 모든 그룹에 대한 집계와 총 합계를 구하는 함수

CUBE(중간중간 부서별, 직급별마다 합계가 들어있다. 결과행이 더 많아짐)

SELECT
       DEPT_CODE
     , JOB_CODE
     , SUM(SALARY)
  FROM EMPLOYEE
 GROUP BY CUBE(DEPT_CODE, JOB_CODE)
 ORDER BY 1;

 

GROUPING 함수

- ROLLUP이나 CUBE에 의한 산출물이 인자로 전달받은 컬럼 집합의 산출물이면 0을 반환하고, 아니면 1을 반환하는 함수

SELECT
       DEPT_CODE
     , JOB_CODE
     , SUM(SALARY)
     , GROUPING(DEPT_CODE) "부서별그룹묶인상태"
     , GROUPING(JOB_CODE) "직급별그룹묶인상태"
  FROM EMPLOYEE
 GROUP BY CUBE(DEPT_CODE, JOB_CODE)
 ORDER BY 1;

 

SET OPERATION(집합 연산)
UNION

- 여러 개의 쿼리 결과를 하나로 합치는 연산자이다. 중복 된 영역을 제외하여 하나로 합친다.

SELECT
       EMP_ID
     , EMP_NAME
     , DEPT_CODE
     , SALARY
  FROM EMPLOYEE
 WHERE DEPT_CODE = 'D5'
 UNION
SELECT
       EMP_ID
     , EMP_NAME
     , DEPT_CODE
     , SALARY
  FROM EMPLOYEE
 WHERE SALARY > 3000000;

 

UNION ALL

- 여러 개의 쿼리를 하나로 합치는 연산자
UNION과의 차이점은 중복 영역을 모두 포함 시킨다.

(위의 값에서 중복된 영역이 두번 나온다.)

SELECT
       EMP_ID
     , EMP_NAME
     , DEPT_CODE
     , SALARY
  FROM EMPLOYEE
 WHERE DEPT_CODE = 'D5'
 UNION ALL
SELECT
       EMP_ID
     , EMP_NAME
     , DEPT_CODE
     , SALARY
  FROM EMPLOYEE
 WHERE SALARY > 3000000;

 

INTERSECT

- 여러 개의 SELECT한 결과에서 공통 부분만 결과로 추출
- 수학에서 교집합과 비슷하다.

(위의 값에서 중복된 값만 추출)

SELECT
       EMP_ID
     , EMP_NAME
     , DEPT_CODE
     , SALARY
  FROM EMPLOYEE
 WHERE DEPT_CODE = 'D5'
INTERSECT
SELECT
       EMP_ID
     , EMP_NAME
     , DEPT_CODE
     , SALARY
  FROM EMPLOYEE
 WHERE SALARY > 3000000;

 

MINUS

- 선행 SELECT 결과에서 다음 SELECT 결과와 겹치는 부분을 제외한 나머지 부분만 추출, 수학에서 차집합과 비슷하다.

(앞의 결과 6명에서 뒤의 결과 2명이 빠진 결과 -> 4명의 값)

SELECT
       EMP_ID
     , EMP_NAME
     , DEPT_CODE
     , SALARY
  FROM EMPLOYEE
 WHERE DEPT_CODE = 'D5'
 MINUS
SELECT
       EMP_ID
     , EMP_NAME
     , DEPT_CODE
     , SALARY
  FROM EMPLOYEE
 WHERE SALARY > 3000000;

 

GROUPING SETS

- 그룹별로 처리 된 여러 개의 SELECT문을 하나로 합칠 때 사용한다. SET OPERATION과 결과 동일함

SELECT
       DEPT_CODE
     , JOB_CODE
     , MANAGER_ID
     , FLOOR(AVG(SALARY))
  FROM EMPLOYEE
 GROUP BY GROUPING SETS((DEPT_CODE, JOB_CODE, MANAGER_ID)
                      , (DEPT_CODE, MANAGER_ID)
                      , (JOB_CODE, MANAGER_ID)
                       );