그룹 함수 — 그룹화 (GROUP BY 절 / HAVING 절)

 

목차

📍   그룹 함수

1.   MIN / MAX

2.   SUM / AVG

    –   NULL의 유무에 따른 평균 값 구하기

3.   COUNT

📍   GROUP BY 절

1.   그룹 기준이 하나일 경우

2.   그룹 기준이 여러 개일 경우

📍   HAVING 절

---

💡 SELECT 쿼리문

    1.   작성 순서
    2.   실행 순위

📝   TEST 1

📝   TEST 2

 

 

 

 

 

 

 

 

 

 

📍   그룹 함수 (여러행 함수)

–  그룹 단위로 값을 받아서 연산하는 함수.

–   GROUP BY절을 사용하지 않는 경우,  테이블의 모든 행이 하나의 그룹으로 취급되어 결과가 만들어짐.

–   그룹 함수 모두에 null값은 연산에서 제외함.

 

 

 

 

 

 

 

 

 

 

▸   MIN   /   MAX

–   행 그룹에서 최소값, 최대값을 구해줌.
–   모든 데이터타입에는 대소관계가 있으므로 모든 종류의 데이터타입 사용가능.

---

EX1   )   숫자 데이터

 

•   최소 급여 & 최대 급여 구하기

SELECT min(salary) AS "최소 급여", max(salary) AS "최대 급여"
FROM employees;

 

–   GROUP BY 절을 사용하지 않았으므로 테이블의 모든 행이 '하나의 그룹' 으로 취급됨.

     →   employees 테이블 자체가 그룹이 됨.

✓   실행 결과

 

 

 

EX2   )   날짜 데이터

 

•   가장 오래된 입사일 & 가장 최근 입사일 구하기

SELECT min(hire_date) AS "가장 오래된 입사일",
	   max(hire_date) AS "가장 최근 입사일"
FROM employees;

–   날짜 데이터는 '이전 날짜' 일수록 최소값,  '이후 날짜' 일수록 최대값을 출력함.

     →   비교 연산을 할 때도 이후 날짜일수록 큼.

✓   실행 결과

 

 

 

EX3   )   문자열 데이터

 

•   제일 앞에 있는 이름 & 제일 뒤에 있는 이름 구하기

SELECT min(last_name) AS "name1", max(last_name) AS "name2"
FROM employees;

–   문자열 데이터에 MIN,  MAX 함수를 사용하면,

     a, b, c 순으로 a가 최소값 z에 가까워질수록 최대값으로 출력함.

✓   실행 결과

 

 

 

 

 

 

 

 

 

 

▸   SUM   /   AVG

–   행그룹의 합계,  평균을 구해줌.
–   연산이 되는 숫자 데이터만 가능.

---

EX1   )

 

•   급여의 합계와 평균 구하기

SELECT sum(salary) AS "급여 합계", avg(salary) AS "평균 급여"
FROM employees;

✓   실행 결과

 

 

 

EX2   )   WHERE절에 LIKE 연산자 사용

 

•   job_id에 'REP'가 들어가는 사원들의 급여 합계 & 평균 구하기

SELECT sum(salary) AS "급여 합계", avg(salary) AS "평균 급여"
FROM employees
WHERE job_id LIKE '%REP%';

–   절 들의 실행 순서 상,  WHERE 절이 먼저 동작 하면서
     WHERE 절의 조건을 만족하는 사원들에 대해 그룹 함수의 연산을 수행함.
–   이제는 전 사원이 그룹이 아닌,  조건값에 맞는 사원들로 그룹화 됨.

✓   실행 결과

 

 

 

 

EX3   )   IFNULL 함수 사용

 

•   NULL의 유무에 따른 평균 값 구하기

SELECT avg(commission_pct), avg(ifnull(commission_pct, 0))
FROM employees;

–    NULL 값을 연산에서 제외하는 규칙은 그룹함수 모두에 적용되는 규칙.
–    avg(commission_pct)는 NULL 을 제외한 값 35개만 사용하여 평균을 연산함.

–    avg(ifnull(commission_pct, 0))은 NULL 값을 0으로 처리한 후 평균을 연산하는데,
      0은 NULL이 아니기 때문에 평균을 계산하는데 활용이 되므로 107명에 대한 평균을 연산하게 됨.

✓   실행 결과

 

 

 

 

 

 

 

 

 

 

▸   count

count (*)	–   다양한 데이터타입 사용 가능.   –   행그룹에서 '모든' 행의 개수를 반환 .   –   null값, 중복값 모두 포함.
count (행그룹)	–   행그룹에서 '행의 개수'를 반환.   –   null값 제외, 중복값만 포함.
count (distinct 행그룹)	–   행그룹에서 '중복 없는 행의 개수'를 반환.   –   null값, 중복값 모두 제외.

---

EX1   )   COUNT(*)   /   COUNT(행그룹)

SELECT count(*), count(commission_pct)
FROM employees;

–   count(commission_pct)는 commission_pct컬럼이 NULL 값을 가지고 있는 컬럼이기 때문에 
       NULL 값들을 제외한 값을 카운트해서 결과를 출력했음.

✓   실행 결과

 

 

 

EX2   )   COUNT(행그룹)

SELECT count(department_id)
FROM employees;

–   전 사원 수는 107명이나 결과값은 106명이 나옴.
     즉,  1명은 department_id가 NULL이란 뜻이며 아마도 사장일 것.

✓   실행 결과

 

 

 

EX3   )   COUNT(DISTINCT 행그룹)

SELECT DISTINCT department_id
FROM employees;

–   DISTINCT 키워드로 인해 중복값을 제거한 부서 개수가 12개 출력됨. (NULL 포함)

✓   실행 결과

 

SELECT count(department_id), count(DISTINCT department_id)
FROM employees;

–   COUNT 함수 + 행그룹을 입력하면 NULL 제외,  중복 값은 포함한 행의 수가 출력됨.

–   COUNT  함수 + DISTINCK 키워드 + 행그룹을 입력하면 NULL 제외,  중복 값도 제외됨.

✓   실행 결과

 

 

 

 

 

 

 

 

 

 

---

 

 

 

 

 

 

 

 

 

 

📍   GROUP BY 절

–   그룹 단위의 기준을 설정하는 절.
–   기준이 되는 컬럼이나 연산의 결과값이 동일한 행들은 같은 그룹으로 묶이게 됨.
–   그룹 함수를 사용하는 경우,

     SELECT 절에 출력할 컬럼에는 그룹 함수와 GROUP BY 절에 사용된 컬럼만 작성 가능한 제약이 생김.
     →   부서 기준으로 그룹 해놨는데 갑자기 사원 번호로 출력하겠다고 하면 기준이 이상해지기 때문.  (출력 개수 다름)
–   GROUP BY절의 컬럼을 SELECT절에 작성하는 건, 각 그룹의 이름값을 출력하는 목적으로 사용되는 것.

---

EX1   )   그룹 기준이 하나일 경우

SELECT department_id, count(*), avg(salary)
FROM employees
GROUP BY department_id;

–   COUNT(*) 함수로 각 부서안에 몇 명의 사원이 있는지 알 수 있음.
     즉, 해당 그룹에 몇 명이 묶여있는지 알 수 있음.
–   AVG(salary)로는 각 부서안의 사원들의 평균 급여를 알 수 있음.
–   GROUP BY 절에 사용한 컬럼을 SELECT 절에 올리는 것은 '그룹 이름' 느낌으로 붙여준 것,  필수 아님.

✓   실행 결과

 

 

 

EX2   )   그룹 기준이 여러 개일 경우

SELECT department_id, job_id, count(*), sum(salary)
FROM employees
WHERE department_id > 40
GROUP BY department_id, job_id
ORDER BY department_id;

 

–   GROUP BY 절에 여러 컬럼이 기준으로 적용된 경우, 기준 컬럼들의 값들이 모두 일치해야 동일 그룹으로 묶임.
     컬럼의 값들 중 하나라도 다른 값이 있는 경우 다른 그룹으로 묶임.
–   같은 부서 안에서 같은 직무일 경우에만 같은 그룹으로 묶임.
–   COUNT(*) 함수로 해당 그룹에 몇 명이 묶여있는지 알 수 있음.

✓   실행 결과

 

 

 

TEST 1   )

 

Q1   )   employees 테이블로부터 전체 사원들의 커미션 평균을 출력하는 구문을 작성하시오.
            단, 소수점 둘째자리까지 반올림해서 출력하시오.

SELECT round(avg(ifnull(commission_pct, 0)),2) AS avg_comm
FROM employees;

→   commission_pct 컬럼은 null값이 포함되어 있으므로 IFNULL 함수를 사용하여 null값을 '0'으로 바꿔줌.

→   그렇게 나온 값에 AVG 함수를 사용하여 평균을 구하고,  ROUND 함수로 소수점 둘째자리까지 반올림하여 출력함.

✓   실행 결과

 

 

 

Q2   )   employees 테이블로부터 업무(job_id)별 
           최대 급여(Maximun), 최소 급여(Minimun), 급여의 합계(Sum), 평균 급여(Average)를 출력하시오.

SELECT job_id, max(salary) Maximun, min(salary) Minimun, 
       sum(salary) Sum, avg(salary) Average
FROM employees
GROUP BY job_id;

✓   실행 결과

 

 

 

Q3   )   employees 테이블로부터 동일 업무(job_id)를 수행하는 직원 수를 출력하는 구문을 작성하시오.

SELECT job_id, count(*)
FROM employees
GROUP BY job_id;

✓   실행 결과

 

 

 

 

 

 

 

 

 

 

---

 

 

 

 

 

 

 

 

 

 

📍   HAVING 절

–   행그룹 제한 조건절.

     →   그룹 함수에 조건을 추가할 수 있음.

     →   WHERE 절은 테이블의 행에 대한 조건절.
–   그룹들에 대한 조건절로 조건을 만족하는 그룹들만 결과로 출력됨.
–   HAVING 절에서는 '그룹함수의 결과값을 기준' 으로 조건을 만들어야 함.

---

EX   )

SELECT job_id, sum(salary) PAYROLL
FROM employees
WHERE job_id NOT LIKE '%REP%'
GROUP BY job_id
HAVING sum(salary) > 13000
ORDER BY sum(salary);

–   절의 실행 순서는,
     1순위 FROM   /   2순위 WHERE   /   3순위 GROUP BY   /   4순위 HAVING   /   5순위 SELECT   /   6순위 ORDER BY

–   employees 테이블에서 'REP'가 들어가지 않은 job_id를 그룹화하고,

      job_id 그룹들 중,  급여의 총 합이 13000을 초과하는 값을 가진 그룹만 SELECT절의 컬럼을 정렬하여 출력함.

✓   실행 결과

 

 

 

 

 

---

💡   SELECT 쿼리문

 

1.   작성 순서

SELECT [컬럼명 또는 표현식] 
FROM [테이블명, 뷰명]  
WHERE [조건절]  
GROUP BY [그룹할 컬럼] 
HAVING [그룹함수 조건절]   
ORDER BY [정렬열] [ASC/DESC];

2.   실행 순위

1순위     FROM

2순위     WHERE

3순위     GROUP BY

4순위     SELECT

5순위     ORDER BY

---

 

 

 

 

 

TEST 2   )

 

Q1   )    employees 테이블로부터 매니저를 알 수 없는 사원은 제외하고,
             매니저별로 그룹화하여 매니저별 최소 급여를 출력하되, 

             최소 급여가 $6000 이상인 그룹만 출력하시오.

             또한, 최소 급여를 기준으로 내림차순으로 정렬하여 출력하시오.

SELECT manager_id, min(salary)
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
HAVING min(salary) >= 6000
ORDER BY min(salary) DESC;

–   매니저를 알 수 없다는 것은 manager_id가 NULL 이라는 것.

✓   실행 결과

 

 

 

Q2   )    employees 테이블에서 최고 급여와 최저 급여의 차이를 출력하는 구문을 작성하시오.

SELECT max(salary), min(salary), (max(salary)-min(salary)) "DIFFERENCE"
FROM employees;

✓   실행 결과

 

 

 

Q3   )   employees 테이블로부터 사원의 총 수와 
            1995년,  1996년,  1997년,  1998년에 채용된 사원의 수를 표시하는 구문을 작성하시오.

SELECT count(*) "TOTAL",
       sum(if(YEAR(hire_date)=1995, 1, 0)) "1995",
       sum(if(YEAR(hire_date)=1996, 1, 0)) "1996",
       sum(if(YEAR(hire_date)=1997, 1, 0)) "1997",
       sum(if(YEAR(hire_date)=1998, 1, 0)) "1998"
from employees;

–   사원의 이름,  부서,  연봉 등

     모든게 똑같은 사원 (중복)은 없을 것이며 모든 값이 NULL인 사원도 없을 것이므로 COUNT(*) 사용하여 사원 수 출력.

     →   원래 COUNT(*) 함수는 NULL & 중복 값 모두 포함하여 출력함. 

–   IF 함수의 조건식으로 YEAR 함수를 사용하여 원하는 입사 년도를 설정하고 참이면 '1',  거짓이면 '0'을 반환.
–   즉,  '1'의 값이 나왔다는건 해당 년도에 입사했다는 뜻이므로, 
     결과값 '1'을 모두 SUM 함수로 더해주면 해당 년도에 입사한 사람의 수를 알 수 있음.
✓   실행 결과

 

 

•   입사 '월'  알아보기

SELECT month(hire_date) "입사 월", count(*)
FROM employees
GROUP BY month(hire_date)
ORDER BY month(hire_date);

–   이런식으로 몇 월에 입사했는지도 출력할 수 있음.
–   GROUP BY 절에는 컬럼뿐만 아니라 함수나 표현식도 사용 가능,  유연함.

✓   실행 결과