[SQL 활용] 3. GROUP BY절과 HAVING절

문법

SELECT column, group_junction
FROM table
[WHERE condition]
[GROUP BY group__by_expression]
[HAVING group_condition]
[ORDER BY column];

---

1. 그룹함수

AVG(평균, 인수는 무조건 숫자만)

COUNT(행의 개수 카운트, NULL값 제외해서 COUNT 함)

MAX(최대값, 모든 데이터 타입 가능)

MIN(최소값, 모든 데이터 타입 가능)

STDDEV(표준편차)

SUM(합계)

VARIANCE(분산)

count(*) : NULL 값 포함

count(expr) : null값 X, 중복값 O

count(distinct expr) : null값 X, 중복값 X

SELECT AVG(salary), MAX(salary), MIN(salary), SUM(salary)
FROM employees
WHERE job_id LIKE '%REP%';

---

SELECT COUNT(commission_pct)
FROM mployees
WHERE department_id = 80;

---

SELECT count(*)
FROM employees
WHERE employee_id = 80;
-- 모든 컬럼 기준
-- null 값이 있어도 count

---

SELECT count(commission_pct)
FROM employees
WHERE employee_id = 80;
-- 특정 칼럼 기준일 때는 null 값 제외해서 count

---

SELECT count(department_id)
FROM employees;
-- 사원이 소속된 부서의 수를 구하라
-- 중복된 값도 count
-- null 값 제외

---

SELECT count(distinct department_id)
FROM employees;
-- null 값 제외
-- 중복된 값도 제외해서 count

---

2. GROUP BY 절

SELECT department_id, AVG(salary)
FROM employees;

주의할 점

SELECT 절에 있는 column(컬럼) 리스트들 중에서 그룹함수에 포함되어있는 컬럼과 그룹함수에 포하되어있지않는 컬럼이 같이 디스플레이 될려면 적어도 그룹 함수에 포함되지 않는 컬럼을 GROUP BY 절에 포함되어있어야 오류가 발생하지않는다.

---

SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id;

---

SELECT department_id, job_id, SUM(salary)
FROM employees
WHERE department_id > 40
ORDER BY department_id;

---

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

---

SELECT department_id, job_id, AVG(salary)
FROM employees
GROUP BY department_id;

---

SELECT department_id, job_id, AVG(salary)
FROM employees
GROUP BY department_id, job_id;

---

SELECT department_id, COUNT(last_name)
FROM employees;

---

SELECT department_id, COUNT(last_name)
FROM employees
GROUP BY department_id;

---

SELECT department_id, job_id, COUNT(last_name)
FROM employees
GROUP BY department_id;

---

SELECT department_id, job_id, COUNT(last_name)
FROM employees
GROUP BY department_id, job_id;

---

 

3. HAVING 절

행 그룹제한 조건은 WHERE 절

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. employees 테이블로부터 업무(job_id)별 최대 급여(Maximum), 최소 급여(Minimum), 급여의 합계(Sum), 평균 급여(Average)를 출력하시오

SELECT job_id,
       MAX(salary) "Maximum"
       MIN(salary) "Minimum",
       SUM(salary) "Sum",
       AVG(salary) "Average"
FROM employees
GROUP BY jo_id;

---

2. employees 테이블로부터 매니저를 알 수 없는 사원은 제외하고 매니저별로 그룹화하여 매니저별 최소 급여를 출력하되 최소 급여를 기준으로 내림차순으로 정렬하여 출력하시오. ( 최소 급여가 600 이상인 그룹만 출력)

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;