GROUP BY & HAVING

GROUP BY

• 그룹 함수는 전체 데이터를 대상으로 작업하기 때문에 단 한 개의 결과 값만 리턴한다.
• 그룹 함수와 여러 개의 컬럼과 함께 사용하면 출력하는 데이터의 개수가 맞지 않아 오류 발생한다.

여러 개의 그룹을 설정하고 그룹 별로 그룹 함수의 결과 값을 산출하기 위해서는 그룹 함수가 적용될 그룹의 기준을 GROUP BY 절에 기술하여 사용한다.

• GROUP BY 절은 WHERE와 ORDER BY 절 사이에 위치한다.
• GROUP BY 절에는 별칭을 사용할 수 없고 반드시 컬럼명을 적어 주어야 한다.

SELECT DEPT_CODE
FROM EMPLOYEE
GROUP BY DEPT_CODE
ORDER BY DEPT_CODE;

-- 각 부서별 그룹으로 묶어서 부서별 총합을 구한 결과를 조회
SELECT DEPT_CODE, SUM(SALARY)
FROM EMPLOYEE
GROUP BY DEPT_CODE
ORDER BY DEPT_CODE NULLS FIRST;

-- EMPLOYEE 테이블에서 부서별 급여의 합을 조회 (부서별 내림차순 정렬)
SELECT NVL(DEPT_CODE,'부서 없음') AS "부서", 
       SUM(SALARY) AS "급여의 합"
FROM EMPLOYEE
GROUP BY DEPT_CODE
ORDER BY DEPT_CODE DESC;

-- EMPLOYEE 테이블에서 부서별 사원의 수, 보너스를 받는 사원의 수, 급여의 합, 평균 급여, 최고 급여, 최저 급여를 조회(부서별 내림차순)
SELECT NVL(DEPT_CODE, '부서없음') AS "부서 코드",
       COUNT(*)|| '명' AS "사원의 수",
       COUNT(BONUS) || '명' AS "보너스 받는 사원수",
       TO_CHAR(SUM(SALARY), 'FML999,999,999') AS "급여의 합",
       TO_CHAR(CEIL(AVG(NVL(SALARY, 0))),'FML999,999,999')  AS "평균 급여",
       TO_CHAR(MAX(SALARY), 'FML999,999,999') AS "최고 급여",
       TO_CHAR(MIN(SALARY), 'FML999,999,999') AS "최저 급여"
FROM EMPLOYEE
GROUP BY DEPT_CODE -- 별칭 X
ORDER BY DEPT_CODE DESC NULLS LAST;

-- EMPLOYEE 테이블에서 성별 별 사원의 수를 조회
SELECT DECODE(SUBSTR(EMP_NO, 8, 1), 1, '남자', 2, '여자') AS "성별",
       COUNT(*) AS "사원수"
FROM EMPLOYEE
GROUP BY SUBSTR(EMP_NO, 8, 1); -- 컬럼, 계산식, 함수 호출 구문이 올 수 있다.
--GROUP BY "성별 코드"; -- 컬럼, 순번, 별칭은 사용할 수 없다.

-- EMPLOYEE 테이블에서 부서 코드와 직급 코드가 같은 사원의 직원 수, 급여의 합을 조회
SELECT DEPT_CODE AS "부서 코드", 
       JOB_CODE AS "직급 코드",
       COUNT(*) AS "직원수",
       SUM(SALARY) AS "급여의 합"
FROM EMPLOYEE
GROUP BY DEPT_CODE, JOB_CODE -- 여러 컬럼을 제시해서 그룹 기준을 지정할 수 있다. 
ORDER BY DEPT_CODE, JOB_CODE;

HAVING

• HAVING 절은 그룹 함수로 값을 구해올 그룹에 대해 조건을 설정할 때 사용한다. (WHERE 절은 SELECT에 대한 조건이다.)
• HAVING 절은 GROUP BY 절 이전이든 이후이든 어느 곳에 위치해도 상관없다.

-- EMPLOYEE 테이블에서 평균 급여가 300만원 이상인 부서를 조회
SELECT DEPT_CODE, FLOOR(AVG(NVL(SALARY, 0)))
FROM EMPLOYEE
-- WHERE FLOOR(AVG(NVL(SALARY, 0))) >= 3000000 -- 에러 발생
GROUP BY DEPT_CODE
HAVING FLOOR(AVG(NVL(SALARY,0))) >= 3000000
ORDER BY DEPT_CODE;

ROLLUP & CUBE 함수 - 집계함수

• 쿼리 작업 시 소계나 합계 등의 작업을 할 경우 유용하게 사용된다.
• ROLLUP과 CUBE 함수는 그룹 별 산출한 결과 값의 집계를 계산하는 함수이다.
• ROLLUP 함수는 주어진 데이터 그룹의 소계를 구해준다.
• CUBE 함수는 주어진 데이터 그룹의 소계와 전체 총계까지 구해준다.

-- EMPLOYEE 테이블에서 직급 코드와 부서 코드가 같은 사원들의 급여의 합계를 조회
SELECT JOB_CODE, DEPT_CODE, SUM(SALARY)
FROM EMPLOYEE
-- ROLLUP(컬럼1, 컬럼2, ...) -> 컬럼1을 가지고 중간 집게를 내는 함수
--GROUP BY ROLLUP(JOB_CODE, DEPT_CODE ) 
-- CUBE(컬럼1, 컬럼2, ...) -> 전달되는 모든 컬럼을 가지고 중간 집게를 내는 함수
GROUP BY CUBE(JOB_CODE, DEPT_CODE)
ORDER BY JOB_CODE, DEPT_CODE;

---

집합 연산자

• 여러 개의 쿼리문을 가지고 하나의 쿼리문으로 만드는 연산자이다.
• 조회하려고 하는 컬럼의 개수와 이름이 같아야 집합 연산자를 사용할 수 있다.

UNION (합집합)

• 두 쿼리문을 수행한 결과값을 하나로 합쳐서 추출한다. (중복되는 행은 제거)

-- EMPLOYEE 테이블에서 부서 코드가 D5인 사원 또는 급여가 300만원 초과인 사원들의 사번, 직원명, 부서 코드, 급여를 조회
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;

-- 위 쿼리문 대신에 WHERE 대신에 OR 연산자를 사용해서 처리 가능
SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY 
FROM EMPLOYEE
**WHERE DEPT_CODE = 'D5' OR SALARY > 3000000;**

UNION ALL (합집합)

• 두 쿼리문을 수행한 결과값을 하나로 합쳐서 추출한다. (중복되는 행을 제거하지 않음)

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 (교집합)

• 두 쿼리문을 수행한 결과값에 중복된 결과값만 추출한다.

-- EMPLOYEE 테이블에서 부서 코드가 D5 이면서 급여가 300만원 초과인 사원 조회
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;

-- 위 쿼리문 대신에 WHERE 절에 AND 연산자를 사용해서 처리 가능
SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY 
FROM EMPLOYEE
**WHERE DEPT_CODE = 'D5' AND SALARY > 3000000;**

MINUS (차집합)

• 선행 쿼리문의 결과값에서 후행 쿼리문의 결과값을 뺀 나머지 결과값만 추출한다.

-- EMPLOYEE 테이블에서 부서 코드가 D5인 사원들 중에 급여가 300만원 초과인 사원들을 제외해서 조회
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;

-- 위 쿼리문 대신에 WHERE 절에 AND 연산자를 사용해서 처리 가능
SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY 
FROM EMPLOYEE
**WHERE DEPT_CODE = 'D5' AND SALARY <= 3000000;**

GROUPING SETS

• 그룹 별로 처리된 여러 개의 쿼리 결과를 한 번에 합친 결과로 추출한다.

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

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

-- 위 두 개의 쿼리 결과를 한 번에 출력한다.
SELECT DEPT_CODE, 
       JOB_CODE, 
       COUNT(*)
FROM EMPLOYEE
GROUP BY GROUPING SETS(DEPT_CODE, JOB_CODE);

-- 부서별 사원수
SELECT DEPT_CODE, COUNT(*)
FROM EMPLOYEE
GROUP BY DEPT_CODE;

-- 직급별 사원수
SELECT JOB_CODE, COUNT(*)
FROM EMPLOYEE
GROUP BY JOB_CODE;

-- 부서별 사원수
SELECT DEPT_CODE, COUNT(*)
FROM EMPLOYEE
GROUP BY DEPT_CODE;

-- 직급별 사원수
SELECT JOB_CODE, COUNT(*)
FROM EMPLOYEE
GROUP BY JOB_CODE;

SELECT DEPT_CODE, JOB_CODE, COUNT(*)
FROM EMPLOYEE
GROUP BY GROUPING SETS(DEPT_CODE, JOB_CODE)
ORDER BY DEPT_CODE, JOB_CODE;

-- EMPLOYEE 테이블에서 부서 코드, 직급 코드, 사수 사번이 동일한 사원의 부서 코드, 직급 코드, 사수 사번, 급여 평균을 조회
SELECT DEPT_CODE, JOB_CODE, MANAGER_ID, FLOOR(AVG(NVL(SALARY, 0)))
FROM EMPLOYEE
GROUP BY DEPT_CODE, JOB_CODE, MANAGER_ID
ORDER BY DEPT_CODE, JOB_CODE, MANAGER_ID;

-- EMPLOYEE 테이블에서 부서 코드, 사수 사번이 동일한 사원의 부서 코드, 사수 사번, 급여 평균을 조회
SELECT DEPT_CODE, MANAGER_ID, FLOOR(AVG(NVL(SALARY, 0)))
FROM EMPLOYEE
GROUP BY DEPT_CODE,MANAGER_ID
ORDER BY DEPT_CODE,MANAGER_ID;

-- EMPLOYEE 테이블에서 직급 코드, 사수 사번이 동일한 사원의 직급 코드, 사수 사번, 급여 평균을 조회
SELECT JOB_CODE, MANAGER_ID, AVG(NVL(SALARY, 0))
FROM EMPLOYEE
GROUP BY JOB_CODE, MANAGER_ID
ORDER BY JOB_CODE, MANAGER_ID;

SELECT DEPT_CODE, JOB_CODE, MANAGER_ID, COUNT(*), FLOOR(AVG(NVL(SALARY, 0)))
FROM EMPLOYEE
GROUP BY GROUPING SETS((DEPT_CODE, JOB_CODE, MANAGER_ID), (DEPT_CODE,MANAGER_ID), (JOB_CODE, MANAGER_ID))
ORDER BY DEPT_CODE, JOB_CODE, MANAGER_ID;