GROUP BY, HAVING

GROUP BY

---

1. 역할: 데이터를 그룹별로 처리하기 위해 사용한다. GROUP BY 는 데이터를 데이터 전체가 아닌 그룹별로 함수를 사용할 수 있도록 한다. 보통 Aggregate Function과 함께 사용한다.
2. 주의사항: GROUP BY 절에 포함되지 않은 컬럼에는 반드시 함수를 적용해야 한다.
3. 연산순서: WHERE, FROM 다음

-- Example
-- Q. How many employees work in each department?

SELECT dept_no, COUNT(emp_no)
FROM dept_emp
GROUP BY dept_no;

 dept_no | count
---------+-------
 d001    | 20211
 d002    | 17346
 d003    | 17786
 d004    | 73485
 d005    | 85707
 d006    | 20117
 d007    | 52245
 d008    | 21126
 d009    | 23580

HAVING

---

그룹을 필터링하고 싶을 때 사용한다.

WHERE 은 각각의 row 마다 필터를 적용하고, HAVING은 그룹마다 필터를 적용한다.

연산순서는 FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY 순이다.

SELECT <column1>, AGGREGATE_FUNCTION(<column2>)
FROM <table>
WHERE <expression>
GROUP BY <column1>
HAVING <expression>;

-- Example
-- Q1. Show the number of female employees working in each department 
-- with department name.

SELECT dp.dept_name, COUNT(e.emp_no)
FROM employees AS e
JOIN dept_emp AS de ON e.emp_no = de.emp_no
JOIN departments AS dp ON de.dept_no = dp.dept_no
WHERE e.gender = 'F'
GROUP BY dp.dept_name;

-- Q2. Show departments from Q1 that have more than 25000 employees.

SELECT dp.dept_name, COUNT(e.emp_no)
FROM employees AS e
JOIN dept_emp AS de ON e.emp_no = de.emp_no
JOIN departments AS dp ON de.dept_no = dp.dept_no
WHERE e.gender = 'F'
GROUP BY dp.dept_name
HAVING COUNT(e.emp_no) > 25000
ORDER BY COUNT(e.emp_no);

-- Q1
     dept_name      | count
--------------------+-------
 Customer Service   |  9448
 Development        | 34258
 Finance            |  7015
 Human Resources    |  7075
 Marketing          |  8037
 Production         | 29549
 Quality Management |  8078
 Research           |  8439
 Sales              | 20854

--Q2
  dept_name  | count
-------------+-------
 Production  | 29549
 Development | 34258