오라클 - GROUP & HAVING

실습에 사용중인 데이터값

 

ORDER BY

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

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

-- 표현식
SELECT 컬렴 명 [, 컬럼명, ...]
FROM 테이블 명
WHERE 조건식
ORDER BY 컬렴명 | 별칭 컬럼 순번 정렬방식 [NULLS FIRST | LAST];

 

 

--사원 전체 이름, 부서코드 조회하기 내림차순으로 정렬 
SELECT
       EMP_NAME
       DEPT_CODE
       FROM EMPLOYEE
       ORDER BY EMP_NAME ASC; -- 오름차순
       -- ORDER BY EMP_NAME DESC; 내림차림
       
--사원 전체 이름, 부서코드 조회하기 오름차순으로 정렬 
SELECT
       EMP_NAME
       DEPT_CODE
       FROM EMPLOYEE
       -- ORDER BY EMP_NAME ASC; -- 오름차순
       ORDER BY EMP_NAME DESC; 내림차림

 

GROUP BY

그룹 함수는 단 한개의 결과 값만 산출하기 때문에 그룹이 여러 개일 경우 오류 발생

여러 개의 결과 값을 산출하기 위해 그룹 함수가 적용될 그룹의 기준을 GROUP BY 절에 기술하여 사용

 

에러발생 

SELECT
       DEPT_CODE
     , SUM(SALARY)
  FROM EMPLOYEE;

 

DEPT_CODE는 총 23개의 행이 나오는데 

SUM 그룹함수는 1개의 결과값만나오기떄문에 출력되는것이 일치하지않는다 . 그래서 에러가발생함

 

해결방법

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

부서별 총 월급

예제1

-- EMPLOYEE테이블에서 부서코드, 그룹 별 급여의 합계, 그룹 별 급여의 평균(정수처리),
-- 인원수를 조회하고 부서 코드 순으로 정렬

SELECT
       DEPT_CODE
     , SUM(SALARY) AS "합계"
     , FLOOR(AVG(SALARY)) AS "평균"
     , COUNT(*)    AS "인원수"
  FROM EMPLOYEE
  GROUP BY DEPT_CODE
  ORDER BY DEPT_CODE;
	   

예제2

-- EMPLOYEE테이블에서 부서코드와 보너스 받는 사원 수 조회하고 부서코드 순으로 정렬
SELECT
       DEPT_CODE
     , COUNT(BONUS)
  FROM EMPLOYEE
GROUP BY DEPT_CODE
ORDER BY DEPT_CODE;

예제3

-- EMPLOYEE테이블에서 성별과 성별 별 급여 평균(정수처리), 급여 합계, 인원 수 조회하고
-- 인원수로 내림차순 정렬
SELECT
       DECODE(SUBSTR(EMP_NO, 8, 1), '1','남', '2', '여') AS "성별"
     , FLOOR(AVG(SALARY)) AS "평균"
     , SUM(SALARY)        AS "합계"
     , COUNT(DECODE(SUBSTR(EMP_NO, 8, 1), '1', 1, '2', 2)) AS "인원수"
       FROM EMPLOYEE
       GROUP BY DECODE(SUBSTR(EMP_NO, 8, 1), '1','남', '2', '여');

예제4

-- EMPLOYEE테이블에서 부서 코드 별로 같은 직급인 사원의 급여 합계를 조회하고
-- 부서 코드 순으로 정렬
SELECT
       DEPT_CODE
     , JOB_CODE
     , SUM(SALARY) 
  FROM EMPLOYEE
GROUP BY DEPT_CODE, JOB_CODE
ORDER BY DEPT_CODE, JOB_CODE;

 

HAVING

그룹 함수로 값을 구해올 그룹에 대해 조건을 설정할 때 HAVING절에 기술

GROUP BY 와 세트로 움직여야함 

 

예제

- 부서 코드와 급여 3000000 이상인 직원의 그룹별 평균 조회
SELECT
       DEPT_CODE
     , AVG(SALARY)
 
  FROM EMPLOYEE
 WHERE SALARY >= 3000000
GROUP BY DEPT_CODE
ORDER BY 1;

 

-- 부서 코드와 급여 평균이 3000000 이상인 그룹 조회
SELECT
       DEPT_CODE
     , FLOOR(AVG(SALARY))
  FROM EMPLOYEE
GROUP BY DEPT_CODE
HAVING FLOOR(AVG(SALARY)) >= 3000000
ORDER BY 1;

 

ROLLUP, CUBE

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

 

ROLLUP

인자로 전달받은 그룹 중 가장 먼저 지정한 그룹별로 추가적 집계 결과 반환

 

예제1

-- EMPLOYEE 테이블에서 각 부서 마다 직급 별 급여합,
-- 부서 별 급여 합, 전체 직원 급여 총합 조회
SELECT
       DEPT_CODE
     , JOB_CODE
     , SUM(SALARY)
     FROM EMPLOYEE
     GROUP BY ROLLUP (DEPT_CODE, JOB_CODE);

 

CUBE

인자로 지정된 그룹들로 가능한 모든 조합 별로 집계한 결과 반환

 

예제1

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

 

GROUPING

ROLLUP이나 CUBE에 의한 집계 산출물이

인자로 전달받은 컬럼 집합의 산출물이면 0 반환 아니면 1 반환

SELECT
       DEPT_CODE
     , JOB_CODE
     , SUM(SALARY)
     , GROUPING(DEPT_CODE) AS "부서별"
     , GROUPING(JOB_CODE) AS "직급별"
     FROM EMPLOYEE
     GROUP BY CUBE (DEPT_CODE, JOB_CODE);

 

GROUPING  변환

알아보기 쉽게 변환해보겠습니다.

    SELECT
       DEPT_CODE
     , JOB_CODE
     , SUM(SALARY)
     , CASE
        WHEN GROUPING(DEPT_CODE) = 0 AND GROUPING(JOB_CODE) = 1 THEN '부서별 합계'
        WHEN GROUPING(DEPT_CODE) = 1 AND GROUPING(JOB_CODE) = 0 THEN '직급별 합계'
        WHEN GROUPING(DEPT_CODE) = 1 AND GROUPING(JOB_CODE) = 1 THEN '총 합계'
        ELSE '그룹별 합계'
      END AS "구분"
     FROM EMPLOYEE
     GROUP BY CUBE (DEPT_CODE, JOB_CODE)
     ORDER BY 1, 2;