MySQL(4) - SELECT 그룹화(집계 함수, GROUP BY, HAVING)

<목차>

1) SELECT 그룹화

   1-1) 집계 함수

   1-2) GROUP BY

   1-3) HAVING

   1-4) 예제

 

 

 

 

 

1) SELECT 그룹화

/*
	* SELECT문 작성 순서
	  SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY (반드시 순서대로)

	* SELECT문 실행 순서
	  FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY

	* 다중 행 함수 = 집계 함수
	  COUNT(), SUM(), MAX(), MIN(), AVG(), ...

	* DML - SELECT 그룹화
	  SELECT [GROUP BY 절에 지정된 컬럼1] [GROUP BY별로 집계할 값] ...
	  FROM [테이블 명] GROUP BY [그룹으로 묶을 컬럼 값] ... HAVING [조건식]
	  => SELECT CLASS, SUM(SCORE) FROM UNIV_CLASS GROUP BY CLASS HAVING SUM(SCORE) >= 150;
*/

 

 

1-1) 집계 함수

/*
	** 집계 함수 : 여러 개의 행의 데이터를 계산하여 값을 내는 함수
	
	GROUP BY : 그룹을 묶는 단위
	HAVING : 그룹함수 결과에 대한 조건
	
	count(), sum(), max(), min(), avg()
*/

 

 

select count(*) from employees;

 

 

select sum(salary) from employees;

 

 

select max(salary) from employees;

 

 

select min(salary) from employees;

 

 

select avg(salary) from employees;

 

 

-- first_name이 5글자인 사원의 수
select 
	count(*)
from employees
where 
	length(first_name) = 5;

 

 

-- 부서번호가 50번인 사원들 중 최대 급여
select 
	max(salary)
from employees
where department_id = 50;

 

 

-- 80번 부서의 평균 급여
select 
	avg(salary)
from employees
where department_id = 80;

 

 

/*
	1. 회사 전체의 최대 연봉, 최소 연봉, 연봉 총합 및 평균 연봉을 자연수로 포맷(반올림)하여 조회한다.
	2. 매니저로 근무하는 사원들의 총 수를 조회한다.
	3. 사내의 최대 연봉 및 최소 연봉의 차이를 조회한다.
*/

 

-- 1. 회사 전체의 최대 연봉, 최소 연봉, 연봉 총합 및 평균 연봉을 자연수로 포맷(반올림)하여 조회한다.
select
	max(salary) as '최대 연봉',
	min(salary) as '최소 연봉',
	sum(salary) as '연봉 총합',
	round(avg(salary), 0) as '평균 연봉'
from employees;

 

 

 

-- 2. 매니저로 근무하는 사원들의 총 수를 조회한다.
select
	count(distinct manager_id)
from employees;

 

 

 

-- 3. 사내의 최대 연봉 및 최소 연봉의 차이를 조회한다.
select
	max(salary) - min(salary) as '연봉 차이'
from employees;

 

 

 

 

1-2) GROUP BY

/*
	GROUP BY
	group에 따라 행을 집약해 결과를 도출
	하나 이상의 컬럼을 기준으로 컬럼값에 따라 그룹화하여 그룹별로 출력
*/

 

-- 사원번호, 전체 연봉의 총합, 전체 평균 연봉
select
	employee_id,
	sum(salary),
	avg(salary)
from employees
group by employee_id;

 

 

 

-- 방법1. 서브쿼리
select
	employee_id,
	(select sum(salary) from employees),
	(select avg(salary) from employees)
from employees;

 

 

 

-- 방법2. OVER()
select
	employee_id,
	sum(salary) over(),
	avg(salary) over()
from employees;

 

 

 

select
	distinct department_id,
	avg(salary) over(partition by department_id) as '부서 평균'
from employees;

 

 

 

-- 부서별 인원수, 급여 총합, 급여 평균
select 
	department_id,
	count(employee_id),
	sum(salary),
	avg(salary)
from employees
group by department_id;

 

 

 

-- 직업별 직원수, 최대급여, 최소급여, 평균급여
select
	job_id,
	count(*) as '직원수',
	max(salary) as '최대급여',
	min(salary) as '최소급여',
	avg(salary) as '평균급여'
from employees
group by job_id;

 

 

 

-- 2개 이상 그룹화
select
	department_id as '부서번호',
	job_id as '직업',
	sum(salary) as '급여총합'
from employees
group by department_id, job_id
order by department_id;

 

 

 

select
	department_id as '부서번호',
	job_id as '직업',
	manager_id as '상사번호',
	sum(salary) as '급여총합'
from employees
group by department_id, job_id, manager_id
order by department_id;

 

 

 

 

1-3) HAVING

/*
	HAVING
	group by 절 뒤에 기술하며 where 절에서 그룹함수를 사용할 수 없기 때문에
	having 절을 통해 그룹함수에 대한 조건을 사용할 수 있다.
*/

 

select
	department_id as '부서번호',
	round(avg(salary)) as '평균급여'
from employees
where department_id is not null
group by department_id
having avg(salary) <= 7000;

 

 

 

/*
	직업에 'REP'라는 문자열이 없으며,
	급여 총액이 13000을 초과하는 직업의
	job_id와 급여총액을 조회하시오.
*/

select
	job_id,
	sum(salary) as '급여총액'
from employees
where
	job_id not like '%REP%'
group by job_id
having
	`급여총액` > 13000
;

 

 

 

-- rollup : 알아서 "GROUP BY" 절에 있는 컬럼을 Grouping 시킨다고 보면 됨!
-- 총계, 소계를 출력할 때 사용
select
	ifnull(department_id, '총계') as '부서번호',
	ifnull(manager_id, '소계') as '매니저번호',
	sum(salary) as '합계'
from employees
where
	department_id is not null
	and
	manager_id is not null
group by
	department_id, manager_id
with rollup
order by department_id desc;

 

 

 

 

1-4) 예제

/*
	1.  직업이 AD_PRES 인 사람은 A 등급을, ST_MAN 인 사람은 B 등급을
	  , IT_PROG 인 사람은 C 등급을, SA_REP 인 사람은 D 등급을, 
      ST_CLERK 인 사람은 E 등급을, 기타는 0 을 부여하여 조회한다.
      조회할 컬럼 : FIRST_NAME, LAST_NAME, JOB_GRADE

	2. 핸드폰 번호가 515로 시작하는 사원들의 사번, 
	  Full Name, 핸드폰 번호를 이름(알파벳)순으로 조회한다.

	3. 각 JOB_ID 별, 최대 연봉, 최소 연봉, 연봉 총 합 및 평균 연봉을 자연수로 포맷하여 조회한다.
	  (JOB_ID 오름차순으로 정렬)

	4. 동일한 직업군을 가진 사원들의 총 수를 조회한다.

	5. 부서번호가 있는 사원들 중 부서별 연봉의 총 합을 조회한다.
	  (부서별 연봉의 총 합은 10000이상이며, 부서번호의 오름차순으로 정렬)

	6. 매니저(상급자)가 있는 사원들 중 최소 연봉을 받는 사원의 연봉을 조회한다. (사원의 매니저별 최소 연봉)
      매니저가 없는 사람들은 제외, 최소 연봉이 6000 미만인 경우는 제외
      연봉 기준 역순으로 조회한다.

	7. EMPLOYEES 테이블에서 부서 인원이 4명보다 많은 부서의 
	  부서번호, 인원수, 급여의 합을 구하여 출력하여라.

	8. EMPLOYEES 테이블에서 최대 급여가 10000이상인 부서에 대해서 
	  부서번호, 평균 급여, 급여의 합을 구하여 출력하여라.
*/

 

-- 1.  직업이 AD_PRES 인 사람은 A 등급을, ST_MAN 인 사람은 B 등급을
--      , IT_PROG 인 사람은 C 등급을, SA_REP 인 사람은 D 등급을, 
--      ST_CLERK 인 사람은 E 등급을, 기타는 0 을 부여하여 조회한다.
--     조회할 컬럼 : FIRST_NAME, LAST_NAME, JOB_GRADE

select 
	first_name,
	last_name,
	(
		case job_id
			when 'AD_PRES' then 'A 등급'
			when 'ST_MAN' then 'B 등급'
			when 'IT_PROG' then 'C 등급'
			when 'SA_REP' then 'D 등급'
			when 'ST_CLERK' then 'E 등급'
			else 0
		end
	) as 'JOB_GRADE'
from employees;

 

 

 

-- 2. 핸드폰 번호가 515로 시작하는 사원들의 사번, 
-- Full Name, 핸드폰 번호를 이름(알파벳)순으로 조회한다.
select
	employee_id,
	concat(first_name, '  ', last_name) as 'Full Name',
	phone_number
from employees
where phone_number like '515%'
order by `Full Name`;

 

 

 

-- 3. 각 JOB_ID 별, 최대 연봉, 최소 연봉, 연봉 총합 및 평균 연봉을 자연수로 포맷하여 조회한다.
-- (JOB_ID 오름차순으로 정렬)
select
	job_id, 
	max(salary) as '최대 연봉',
	min(salary) as '최소 연봉',
	sum(salary) as '연봉 총합',
	round(avg(salary)) as '평균 연봉'
from employees
group by job_id
order by job_id;

 

 

 

-- 4. 동일한 직업군을 가진 사원들의 총 수를 조회한다.
select
	job_id,
	count(employee_id)
from employees
group by job_id;

 

 

 

-- 5. 부서번호가 있는 사원들 중 부서별 연봉의 총 합을 조회한다.
-- (부서별 연봉의 총 합은 10000이상이며, 부서번호의 오름차순으로 정렬)
select
	department_id,
	sum(salary) as '부서별 연봉 총합'
from employees
where department_id is not null
group by department_id
having `부서별 연봉 총합` >= 10000
order by department_id;

 

 

 

-- 6. 매니저(상급자)가 있는 사원들 중 최소 연봉을 받는 사원의 연봉을 조회한다. (사원의 매니저별 최소 연봉)
--     매니저가 없는 사람들은 제외, 최소 연봉이 6000 미만인 경우는 제외
--     연봉 기준 역순으로 조회한다.
select
	manager_id,
	min(salary) as '사원의 매니저별 최소 연봉'
from employees
where manager_id is not null
group by manager_id
having `사원의 매니저별 최소 연봉` >= 6000
order by `사원의 매니저별 최소 연봉` desc;

 

 

 

-- 7. EMPLOYEES 테이블에서 부서 인원이 4명보다 많은 부서의 
-- 부서번호, 인원수, 급여의 합을 구하여 출력하여라.
select
	department_id,
	count(*) '인원수',
	sum(salary) as '급여 총합'
from employees
group by department_id
having `인원수` > 4;

 

 

 

-- 8. EMPLOYEES 테이블에서 최대 급여가 10000이상인 부서에 대해서 
-- 부서번호, 평균 급여, 급여의 합을 구하여 출력하여라.
select
	department_id,
	round(avg(salary)) as '평균 급여',
	sum(salary) as '급여 총합'
from employees
group by department_id
having max(salary) >= 10000;