그룹함수, GROUP BY, HAVING, ORDER BY

그룹함수

1. 그룹함수 : 특정컬럼을 기준으로 종합한 내용을 출력하는것. (합계, 평균, 데이터 수, 최대값, 최소값 등)

 

2. RESULT SET이 기본적으로 한개가 출력된다. 👏 그룹으로 묶어서 출력할 경우 그룹별로 결과 출력

 

3. 그룹함수는 집계하는 컬럼을 제외한 다른컬럼은 SELECT문에서 선택할 수 없다.

   EX) SELECT EMP_NAME, SUM(SALARY), FROM EMPLOYEE; -- 불가능

 

4. 그룹함수의 종류

    1) SUM(COULMN) : 매개변수 컬럼의 총 합계를 구하는 기능

-- 전 사원의 월급 합계 구하기
SELECT SUM(SALARY)
FROM EMPLOYEE;

    2) AVG(COULMN) : 매개변수 컬럼의 평균을 구하는 기능

-- 부서코드가 D5인 사원들의 급여 평균을 구해보자
SELECT AVG(SALARY)
FROM EMPLOYEE
WHERE DEPT_CODE = 'D5';

    3) COUNT(* || COULMN) : ROW의 개수를 출력해주는 함수

        ✔ * : NULL 값을 포함해서 센다. 즉 전체 개수를 출력

        ✔ COULMN : NULL값을 제외하고 센다.

-- 전체 사원 수를 구해보자
SELECT COUNT(*)
FROM EMPLOYEE;

    4) MIN/MAX : 최소/최대값을 구하는 함수

-- 전체 사원 중 가장 높은 급여를 받는 사원의 급여가 얼마인지 구해보자
SELECT MAX(SALARY)
FROM EMPLOYEE;

-- 전체 사원 중 가장 낮은 급여를 받는 사원의 급여가 얼마인지 구해보자
SELECT MIN(SALARY)
FROM EMPLOYEE;

 

5. 그룹함수는 WHERE절에 사용이 불가능하다. => HAVING절에서 사용이 가능하다.

 

 

 

ORDER BY

1. ORDER BY : 데이터(ROW)를 정렬하는 예약어

 

-- 사원의 이름을 이름순으로 정렬해서 조회해보자
SELECT EMP_NAME
FROM EMPLOYEE
ORDER BY EMP_NAME;

-- 급여를 많이 받는 순으로 사원 조회하기
SELECT EMP_NAME, SALARY
FROM EMPLOYEE
ORDER BY SALARY DESC; -- 정순으로 정렬하면 적은수부터 나오기 때문에
					  -- DESC를 사용해서 정렬한다.

2. 컬럼에 부여된 인덱스 번호 및 SELECT문에서 사용한 별칭을 사용 할 수 있다.

-- 컬럼에 부여된 인덱스 번호를 기준으로 정렬하기
SELECT EMP_NAME, SALARY, DEPT_CODE, JOB_CODE
FROM EMPLOYEE
ORDER BY 1; -- 2; 3; 4;
-- 1(EMP_NAME), 2(SALARY)....

-- SELECT문에서 사용한 별칭을 이용하여 정렬하기
SELECT EMP_NAME AS 사원명, SALARY
FROM EMPLOYEE
--WHERE 사원명='선동일'; // WHERE절에서는 별칭 사용 불가
ORDER BY 사원명;

3. 정렬시 NULL 값에 대한 처리

    1) ORDER BY 컬럼명 : 오름차순일 때 NULL을 맨 아래로

    2) ORDER BY 컬럼명 NULLS FIRST : 오름차순일 때 NULL을 맨 위로

    3) ORDER BY 컬럼명 DESC : 내림차순일 때 NULL을 맨 위로

    4) ORDER BY 컬럼명 NULLS LAST : 내림차순일 때 NULL을 맨 아래로

 

 

 

GROUP BY

1. GROUP BY : 특정컬럼을 기준으로 그룹화(묶는다) 시키는 함수. 그룹 단위로 집계한다.

 

2. GROUP BY에 사용한 컬럼은 SELECT문 컬럼으로 사용이 가능하다.

 

3. 예문

-- 부서별 급여 합계를 구해보자
SELECT DEPT_CODE, SUM(SALARY)
FROM EMPLOYEE
GROUP BY DEPT_CODE;

-- 직책별 사원수를 구해보자
SELECT JOB_CODE, COUNT(*) AS 사원수
FROM EMPLOYEE
GROUP BY JOB_CODE;

-- 부서별 급여를 제일 많이 받는 사원의 급여를 조회하기(깔끔하게 출력해보자!)
SELECT DEPT_CODE, TO_CHAR(MAX(SALARY), 'L999,999,999')
FROM EMPLOYEE
GROUP BY DEPT_CODE;

4. GROUP BY 절에는 다수의 컬럼이 올 수 있다.

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

    ✔ D'n' 이면서 J'n'인 그룹을 모두 생성한다.

 

5. ROLLUP / CUBE

    1) ROLLUP : 각 그룹별 합계를 구하고 마지막에 총 합계를 구하는 함수(단, 매개변수가 하나일때)

SELECT DEPT_CODE, SUM(SALARY)
FROM EMPLOYEE
WHERE DEPT_CODE IS NOT NULL
GROUP BY ROLLUP(DEPT_CODE);

    2) CUBE : 총 합계를 구하고 각 그룹별 합계를 구하는 함수(단, 매개변수가 하나일때)

SELECT DEPT_CODE, SUM(SALARY)
FROM EMPLOYEE
WHERE DEPT_CODE IS NOT NULL
GROUP BY CUBE(DEPT_CODE);

6. GROUPING 함수 : ROLLUP이나 CUBE에 의한 집계 산출물이 인자로 전달받은 컬럼 집합의 산출물이면 0반환, 아니면 1반환

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
WHERE DEPT_CODE IS NOT NULL
GROUP BY CUBE(DEPT_CODE, JOB_CODE)
ORDER BY 1;

 

 

HAVING절

1. 그룹 함수는 WHERE절에 사용 할 수 없다. 

 

2. 그래서 사용하는게 HAVING절!!

 

3. 그룹함수에 조건을 걸어줄 때 사용한다.

 

4. 예문

-- 부서별 사원수가 3명 이상인 부서를 출력해보자
SELECT DEPT_CODE, COUNT(*)
FROM EMPLOYEE
-- WHERE COUNT(*) >= 3; -> 불가능하다
GROUP BY DEPT_CODE
HAVING COUNT(*) >= 3;

 

 

 

집합연산자

1. 집합연산자 : 여러개의 SELECT문(RESULT SET)을 합치거나, 중복값을 제거하거나, 중복값만 출력하는 연산

    ✔ 집합연산자는 RESULT SET의 컬럼수와 데이터 타입만 맞으면 데이터 내용이 달라도 합쳐진다.

SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY
FROM EMPLOYEE
WHERE DEPT_CODE='D5'
UNION
SELECT DEPT_ID, DEPT_TITLE, LOCATION_ID, 0
FROM DEPARTMENT
UNION
SELECT JOB_CODE, JOB_NAME, NULL, NULL
FROM JOB;

    1) UNION : 여러개의 SELECT문을 합치는것. 합집합. 중복값을 하나만 출력

SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY
FROM EMPLOYEE
WHERE DEPT_CODE='D5';
-- 의 결과와
SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY
FROM EMPLOYEE
WHERE SALARY>3000000;
-- 의 결과 중 중복되는 값은 '심봉선'하나이다.
-- 이 두 SELECT문을 UNOIN으로 합치면 '심봉선'은 하나만 출력된다.

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;

    2) UNION ALL : 합집합. 중복값을 모두 출력한다.

-- 위와 같은 예문으로 실해하면 '심봉선'이 두명 출력된다
SELECT EMP_NAME, DEPT_CODE, SALARY
FROM EMPLOYEE
WHERE DEPT_CODE='D5'
UNION ALL
SELECT EMP_NAME, DEPT_CODE, SALARY
FROM EMPLOYEE
WHERE SALARY >= 3000000;

    3) INTERSECT : 교집합 두개 이상의 SELECT문에서 중복되는 ROW만 출력

-- 위의 예문을 그대로 사용하면 중복값인 '심봉선'만 출력된다.
SELECT EMP_NAME, DEPT_CODE, SALARY
FROM EMPLOYEE
WHERE DEPT_CODE = 'D5'
INTERSECT
SELECT EMP_NAME, DEPT_CODE, SALARY
FROM EMPLOYEE
WHERE SALARY >= 3000000;

    4) MINUS : 한개 SELECT문에서 다른 SELECT문과 중복되는 데이터를 제외하고 출력

-- 위의 SELECT문에서 아래 SELECT문과 중복되는 '심봉선'을 제외한 결과가 출력된다
SELECT EMP_NAME, DEPT_CODE, SALARY
FROM EMPLOYEE
WHERE DEPT_CODE = 'D5'
MINUS
SELECT EMP_NAME, DEPT_CODE, SALARY
FROM EMPLOYEE
WHERE SALARY >= 3000000;