[Mysql] group by와 having

1. group by란?

group by는 특정 컬럼을 기준으로 그룹화 하는 명령어입니다. 
그룹화를 하면 집계함수를 이용해서 통계데이터(평균, 합계 등)을 구할 수 있습니다.

select 컬럼
from 테이블명
where 조건
group by 그룹컬럼
[having 조건]

 

2. group by 예제

#예제 테이블 및 데이터
CREATE TABLE `department` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(50) NULL DEFAULT NULL,
	PRIMARY KEY (`id`)
)

CREATE TABLE `employee` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(100) NULL DEFAULT NULL,
	`salary` INT(11) NULL DEFAULT NULL,
	`dept_id` INT(11) NULL DEFAULT NULL,
	PRIMARY KEY (`id`),
	CONSTRAINT `fk_department_id` FOREIGN KEY (`dept_id`) REFERENCES `department` (`id`)
)

INSERT INTO `department` (`id`, `name`) VALUES (1, '사업부');
INSERT INTO `department` (`id`, `name`) VALUES (2, '개발부');
INSERT INTO `department` (`id`, `name`) VALUES (3, '영업부');

INSERT INTO `employee` (`id`, `name`, `salary`, `dept_id`) VALUES (6, 'hong', 5500, 1);
INSERT INTO `employee` (`id`, `name`, `salary`, `dept_id`) VALUES (3, 'lee', 7000, 1);
INSERT INTO `employee` (`id`, `name`, `salary`, `dept_id`) VALUES (5, 'jang', 4000, 1);
INSERT INTO `employee` (`id`, `name`, `salary`, `dept_id`) VALUES (8, 'lim', 3400, 2);
INSERT INTO `employee` (`id`, `name`, `salary`, `dept_id`) VALUES (7, 'sin', 2800, 2);
INSERT INTO `employee` (`id`, `name`, `salary`, `dept_id`) VALUES (1, 'kim', 3000, 2);
INSERT INTO `employee` (`id`, `name`, `salary`, `dept_id`) VALUES (4, 'go', 3400, 2);
INSERT INTO `employee` (`id`, `name`, `salary`, `dept_id`) VALUES (2, 'park', 2600, 2);
INSERT INTO `employee` (`id`, `name`, `salary`, `dept_id`) VALUES (9, 'chang', 4400, 3);
INSERT INTO `employee` (`id`, `name`, `salary`, `dept_id`) VALUES (10, 'ho', 3600, 3);

# 데이터 조회
select 
  emp.id,
  emp.name,
  emp.salary,
  dept.name as 'dept_name'
from employee emp
join department dept on (dept.id = emp.dept_id)
order by emp.dept_id

사업부, 개발부, 영업부가 있고 각 부서에 소속된 사원들이 있을 때, 부서 별로 소속된 사원의 연봉 정보(합계, 평균, 최소, 최대)를 얻고 싶다면 다음과 같은 쿼리를 입력하면 됩니다. 

1) 부서 별 연봉 정보 추출 쿼리

select 
  dept.name,
  count(emp.id) as '소속인원',
  sum(emp.salary) as '총 연봉',
  avg(emp.salary) as '평균 연봉',
  min(emp.salary) as '최저 연봉',
  max(emp.salary) as '최고 연봉'
from employee emp
join department dept on (dept.id = emp.dept_id)
group by dept.id
;

employee 테이블과 department 테이블을 조인하고 부서 dept.id 컬럼으로 그룹화를 했습니다. 
그러면 내부적으로 employee.dept_id 컬럼의 같은 값 끼리 그룹핑이 됩니다. 부서별로 그룹핑된 데이터를 이용해서 소속 인원, 총 연봉, 평균 연봉, 최저 연봉, 최고 연봉을 추출할 수 있습니다.

 

2) having

having 명령어를 이용하면 집계된 데이터로 조건을 부여할 수 있습니다. 
집계 함수의 결과를 조건으로 이용하려면 반드시 having 절에서 사용해야 하며 where에서 사용하면 에러가 발생합니다.

#평균 연봉이 5000만원 이상인 부서 정보를 조회해라
select 
  dept.name,
  count(emp.id) as '소속인원',
  sum(emp.salary) as '총 연봉',
  avg(emp.salary) as '평균 연봉',
  min(emp.salary) as '최저 연봉',
  max(emp.salary) as '최고 연봉'
from employee emp
join department dept on (dept.id = emp.dept_id)
group by dept.id
having avg(emp.salary) >= 5000

 

3) 그룹화 하지 않은 컬럼을 조회 할 경우

select 
  dept.name,
  emp.name,
  max(emp.salary) as '최고 연봉'
from employee emp
join department dept on (dept.id = emp.dept_id)
group by dept.id

개발부에서 가장 높은 연봉을 받는 사원은 lim이다

부서 별로 가장 높은 연봉을 받는 사원 정보를 조회하고 싶을 때 위와 같이 조회하는 실수를 범할 수 있습니다.
실제로 위와 같이 조회하면 emp.name에 엉뚱한 이름이 조회가 됩니다.

그룹화 하지 않은 컬럼을 조회할 경우 집계 함수로 감싸야합니다. 여기서 그룹화한 테이블은 department 테이블이고, department의 id로 그룹화 했기 때문에 department 별로 정보를 추출 할 수 있지만 employee 테이블은 그룹화하지 않았기 때문에 employee name을 조회하면 엉뚱한 데이터가 조회됩니다.

부서별로 가장 높은 연봉을 받는 사원을 조회하고 싶다면 아래 링크를 참조해주세요.

2021.03.27 - [IT/Mysql & MariaDB] - [Mysql] group by 시 가장 큰 값을 가진 row 조회하기

 

4) order by

SELECT 쿼리 실행 시 실행 순서는 다음과 같습니다. 
1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. ORDER BY
6. SELECT
7. LIMIT

group by는 order by가 수행되기 전에 실행됩니다. 
집계된 데이터로 정렬을 하고 싶다면 다음과 같이 쿼리를 입력하면 됩니다.

# 부서 별로 최고 연봉을 조회하고 최고 연봉 내림차순으로 정렬하라
select 
  dept.name,
  max(emp.salary) as '최고 연봉'
from employee emp
join department dept on (dept.id = emp.dept_id)
group by dept.id
order by avg(emp.salary) desc