[sql] 5. 그룹함수 | GROUP BY절 | HAVING절

1. 그룹함수의 개념 및 특징

1) 그룹함수의 개념

• 매개변수로 다수의 값을 전달받아 가공처리하여 결과값을 반환하는 함수
• 주의) 딱 한 건의 결과만을 반환
• 즉, 행이 한줄이다 = 한 행의 값으로 반환된다!
• COUNT함수
• MAX함수
• MIN함수
• SUM함수
• AVG함수
• ..

2) 그룹함수의 특징

• 그룹함수는 다른 검색대상과 같이 사용하면 그룹함수와 검색대상의 검색행 갯수가 서로 다르기 때문에 에러 발생함
• 그룹함수끼리 나열해 사용은 가능!

-- COUNT(EMPNO)는 결과값이 1행이고, ENAME은 결과값이 여러행이기 때문 ERROR 발생
SELECT COUNT(EMPNO) ,ENAME FROM EMP; --ERROR

-- COUNT(EMPNO)와 COUNT(COMM)는 결과값이 모두 1행이기 때문에 가능
SELECT COUNT(EMPNO),COUNT(COMM) FROM EMP;  --ok

• 그룹함수는 NULL을 값으로 처리하지 않고 결과값을 반환함

--COUNT(COMM)함수에 NULL을 제외한 값들만 반환받음
SELECT COUNT(EMPNO), COUNT(COMM) FROM EMP; --ok

• 일단, EMP테이블에 저장된 모든 컬럼의 컬럼값들을 출력해주세요!
• 그룹함수를 사용하기 위해 미리 출력해봄

SELECT * FROM EMP;

 

 

 

 

 

 

 

2. COUNT(컬럼명) 함수

• 다수의 컬럼값에 갯수를 반환하는 함수
• 검색행의 갯수 반환
• *사용 가능

-- 1)
-- EMP테이블에 저장된 EMPNO컬럼의 컬럼값이 몇개니?
SELECT COUNT(EMPNO) FROM EMP;
--14개 행이 있어요!


-- 2)
-- 🔥EMP테이블에 저장된 모든 행(*)의 갯수 몇개니?🔥
-- **COUNT함수**는 **컬럼명** 대신 **기호**를 사용해 모든 컬럼 표현 가능 (COUNT함수 유일)
-- **페이징 처리**를 하려면 전체 행의 갯수를 알아야함 (그 때 사용함)
-- ex) 쇼핑몰의 등록된 제품의 갯수? , 가입된 총 회원은 몇명? ..
SELECT COUNT(*) FROM EMP;
--14개 행이 있어요!

 

 

 

 

 

 

 

 

 

3. MAX(컬럼명) 함수

• 다수의 컬럼값 중 최대값을 반환하는 함수

-- 모든 사원들 중(EMP테이블에서) 최대 급여는 얼마?
SELECT MAX(SAL) FROM EMP;

-- 모든 사원들 중(EMP테이블에서) 이름이 ~X에 가까운 사람?
SELECT MAX(ENAME) FROM EMP;

-- 모든 사원들 중(EMP테이블에서) 최근 입사일은 얼마?
SELECT MAX(HIREDATE) FROM EMP;

 

 

 

 

 

 

 

4. MIN(컬럼명) 함수

• 다수의 컬럼값 중 최소값을 반환하는 함수

-- 모든 사원들 중(EMP테이블에서) 최소 급여는 얼마?
SELECT MIN(SAL) FROM EMP;

-- 모든 사원들 중(EMP테이블에서) 이름이 A~에 가까운 사람?
SELECT MIN(ENAME) FROM EMP;

-- 모든 사원들 중(EMP테이블에서) 오래된 입사일은 얼마?
SELECT MIN(HIREDATE) FROM EMP;

 

 

 

 

 

 

 

 

5. SUM(컬럼명) 함수

• 다수의 컬럼값 (숫자값)에 대한 합계를 계산하여 반환하는 함수

-- 모든 사원들(EMP테이블에서)의 급여의 합계는?
SELECT SUM(SAL) FROM EMP;

 

 

 

 

 

 

 

 

6. AVG(컬럼명) 함수

• 다수의 컬럼값(숫자값)에 대한 평균을 계산하여 반환하는 함수

-- 모든 사원들(EMP테이블에서)의 급여의 평균은?
SELECT AVG(SAL) FROM EMP;

--소숫점 두번째 자리까지 반올림
SELECT ROUND(AVG(SAL),2) FROM EMP;

 

 

 

 

 

 

 

 

7. 🔥만약 그룹함수 계산 시 NULL이 있다면?

• NULL은 계산할 수 없으므로 제외되어 계산됨
• 이럴 때는 NULL을 0으로 변환하여 계산 해주어야 함
• NVL( )함수 이용

--1)
--EMP테이블에 저장된 모든 사원의 성과급 평균을 계산하여 검색
--검색실패 : 모든 사원이 아닌 성과급이 NULL이 아닌 사원들끼리의 성과급 평균 계산
SELECT AVG(COMM) FROM EMP;


--2)
--NVL함수를 사용하여 성과급이 NULL인 경우 0으로 변환하여 평균 성과급 계산되도록 검색
SELECT AVG(NVL(COMM,0)) FROM EMP; -- 검색성공
SELECT CEIL(AVG(NVL(COMM,0))) FROM EMP; --검색성공 (반올림)

1)

2)

2)

 

 

 

 

 

 

 

8. GROUP BY절

1) GROUP BY절의 필요성

• 🔥테이블 내 그룹짓기하여 계산하고 싶다면?

-- EMP테이블에 저장된 모든 사원에 대한 인원수 검색
SELECT COUNT(*) FROM EMP;

• 매우 불편하다!! 개선필요!!!! 부서갯수 확인하고, 각각 카운트해야함!!!!!!! → GROUP BY절 도입

--EMP테이블에 저장된 모든 사원을 부서별로 구분하여 인원수 검색
SELECT DISTINCT DEPTNO FROM EMP;
SELECT COUNT(*) FROM EMP WHERE DEPTNO=10;
SELECT COUNT(*) FROM EMP WHERE DEPTNO=20;
SELECT COUNT(*) FROM EMP WHERE DEPTNO=30;

DEPTNO=10

DEPTNO=20

DEPTNO=30

2) GROUP BY절

• 전제조건) 그룹함수 사용 시에만 GROUP BY절 사용 가능!!!!!!!!
• 그룹함수 사용하지 않으면 GROUP BY절도 사용불가능
• 그룹함수 사용 시 컬럼값을 비교하여 그룹을 여러개 구분하여 검색하는 기능
• 컬럼값이 같은 경우 같은 그룹으로 인식되어 처리
• 별칭사용불가능

-- 1st FROM 테이블명 : 해당 테이블을 찾아서
-- 2nd [WHERE] 조건식 : 조건에 맞는
-- 3rd GROUP BY {컬럼명|연산식|함수}, {컬럼명|연산식|함수}, ... : 그룹을 만들어
-- 4th SELECT 그룹함수(컬럼명)[,검색대상,...] : 그룹함수를 통한 알맞은 계산을 하여
-- 5th [ORDER BY{컬럼명|연산식|별칭|COLUMN_INDEX} {ASC|DESC},...] : 정렬해주세요

SELECT 그룹함수(컬럼명)[,검색대상,...]
FROM 테이블명
[WHERE] 조건식
GROUP BY {컬럼명|연산식|함수}, {컬럼명|연산식|함수}, ...
[ORDER BY {컬럼명|연산식|별칭|COLUMN_INDEX} {ASC|DESC},...]

3) GROUP BY절 예제

--1)
--EMP테이블에 저장된 모든 사원을 부서별로 구분하여 인원수 검색
SELECT COUNT(*) FROM EMP GROUP BY DEPTNO;

--2)
-- GROUP BY에서 사용한 그룹표현식{컬럼명|연산식|함수}은 그룹함수와 같이 검색대상으로 사용 가능함
SELECT DEPTNO, COUNT(*) FROM EMP GROUP BY DEPTNO;
-- GROUP BY에 컬럼의 별칭을 사용할 경우 에러 발생
SELECT DEPTNO DNO, COUNT(*) FROM EMP GROUP BY DNO; --ERROR

--3)
-- EMP테이블에서 저장된 모든 사원의 업무별 평균 급여 검색
SELECT JOB, AVG(SAL) FROM EMP GROUP BY JOB;

--4)
SELECT JOB, CEIL(AVG(SAL)) AVG_SAL FROM EMP GROUP BY JOB; -- CEIL함수 + 별칭 이용

--5)
-- EMP테이블에서 업무가 RRESIDENT인 사원을 제외한 모든 사원의 업무별 평균 급여를 평균 급여로 내림차순 정렬하여 검색
SELECT JOB, CEIL(AVG(SAL)) AVG_SAL 	//4th 모든 사원의 업무별 평균 급여를
FROM EMP 							//1st EMP테이블에서
WHERE JOB<>'PRESIDENT' 				//2nd 업무가 RRESIDENT인 사원을 제외한
GROUP BY JOB 						//3rd JOB그룹끼리 그룹지어
ORDER BY AVG_SAL DESC; 				//5rd 평균 급여로 내림차순 정렬하여 검색

1)

2)

3)

4)

5)

 

 

 

 

 

 

 

 

9. HAVING절

• 전제조건) 그룹함수 -> GROUP BY절 -> HAVING절
• GROUP BY에 의해 그룹화된 검색결과에서그룹조건이 참인 그룹만 검색하는 기능
• HAHVING은 GROUP BY가 없다면 사용불가능!!!!

SELECT 그룹함수(컬럼명)[,검색대상,...]
FROM 테이블명
[WHERE 조건식]
GROUP BY {컬럼명|연산식|함수},{컬럼명|연산식|함수},...
HAVING 그룹조건식
[ORDER BY {컬럼명|연산식|별칭|COLUMN_INDEX} {ASC|DESC},...]

 

 

1) HAVING절 예제

--1)
--EMP테이블에 저장된 모든 사원의 부서번호와 부서별 급여 합계 검색
SELECT DEPTNO, SUM(SAL) FROM EMP GROUP BY DEPTNO;


--2)
--EMP테이블에 저장된 모든 사원의 부서별 급여 합계 중,
--급여 합계가 9000 이상인 부서 부서번호와 급여 합계 검색
SELECT DEPTNO, SUM(SAL) FROM EMP GROUP BY DEPTNO HAVING SUM(SAL)>=9000;


--3)
-- EMP테이블에서 업무가 RRESIDENT인 사원을 제외한 모든 사원의 업무별 평균 급여 검색
--(업무가 PRESIDENT인 사원제외)
SELECT JOB, CEIL(AVG(SAL)) AVG_SAL FROM EMP WHERE JOB<>'PRESIDENT' GROUP BY JOB;

--4)
--(그룹이 PRESIDENT인 사원제외)
SELECT JOB, CEIL(AVG(SAL)) AVG_SAL FROM EMP GROUP BY JOB HAVING JOB<>'PRESIDENT';

1)

2)

3)

4)

 

 

 

 

 

 

 

 

 

10. 연습문제

문제1. 사원테이블에서 부서별(DEPTNO) 인원수가 6명 이상인 부서코드 검색

--1)
SELECT DEPTNO,COUNT(*) FROM EMP GROUP BY DEPTNO HAVING COUNT(*)>=6;

--2)
--주의
--SUM(DEPTNO)함수 사용하면 부서코드끼리 합산한 값이 출력됨!!
SELECT DEPTNO, SUM(DEPTNO) FROM EMP GROUP BY DEPTNO HAVING SUM(DEPTNO)>=6; --검색실패

 

문제2. 사원테이블로부터 부서번호, 업무별 급여합계를 계산하고자 한다.

다음과 같은 결과를 출력할 수 있는 SQL문장 작성하시오.

SELECT DEPTNO,
SUM(DECODE(JOB,'CLERK',SAL)) "CLERK",
SUM(DECODE(JOB,'MANAGER',SAL)) "MANAGER",
SUM(DECODE(JOB,'PRESIDENT',SAL)) "PRESIDENT",
SUM(DECODE(JOB,'ANALYST',SAL)) "ANALYST",
SUM(DECODE(JOB,'SALESMAN',SAL)) "SALESMAN"
FROM EMP
GROUP BY DEPTNO
ORDER BY DEPTNO;

 

문제3. 사원테이블로부터 년도별끼리 급여합계, 월별끼리 급여합계를 출력할 수 있는 SQL문장 작성하시오

SELECT TO_CHAR(HIREDATE,'YYYY') "년",
TO_CHAR(HIREDATE,'MM') "월",
SUM(SAL)
FROM EMP
GROUP BY TO_CHAR(HIREDATE,'YYYY'),TO_CHAR(HIREDATE,'MM')
ORDER BY 년,월;

 

문제4. 사원테이블에서 부서별 comm(커미션)을 포함하지 않은 연봉의 합과 포함한 연봉의 합을 구하는 SQL을 작성하시오.

--1)
--성과급 제외
SELECT DEPTNO,SUM(SAL*12) FROM EMP GROUP BY DEPTNO ORDER BY DEPTNO;

--2)
--성과급 포함
SELECT DEPTNO,SUM((SAL+NVL(COMM,0))*12) FROM EMP GROUP BY DEPTNO ORDER BY DEPTNO;

1)

2)

 

문제5.사원테이블에서 SALESMAN을 제외한 JOB별 급여 합계

--방법1.
SELECT JOB,SUM(SAL) FROM EMP WHERE JOB<>'SALESMAN' GROUP BY JOB;
--방법2.
SELECT JOB,SUM(SAL) FROM EMP GROUP BY JOB HAVING JOB<>'SALESMAN';