📘 그룹 조회

📖 GROUP BY 절

• 특정 컬럼 값을 기준으로 테이블의 전체 행을 그룹별로 나누기 위한 절

SELECT {컬럼 [AS 별칭] ... , 그룹함수(컬럼)} FROM 테이블
[WHERE 검색조건]
[GROUP BY 컬럼]
[ORDER BY 컬럼 [정렬옵션]]

• 그룹핑 전에 WHERE 절을 사용하여 그룹 대상을 먼저 선택가능
• GROUP BY 절에는 반드시 컬럼이름이 포함되어야 하며 별명 사용 불가
• SELECT 절에서 집계 함수 없이 나열된 컬럼 이름이나 표현식은 GROUP BY 절에 반드시 포함되어야함.
• GROUP BY 절에 나열된 컬럼 이름은 SELECT 절에 명시하지 않아도 됨
• SELECT 절에서 그룹함수를 사용할 경우 GROUP BY 절에서 나눈 그룹 안에서 집계를 수행한다.

예제 1

• 그룹 조회와 집계 함수의 관계 (1)

SELECT deptno, name FROM professor ORDER BY deptno;

+--------+--------+
| deptno | name   |
+--------+--------+
|    101 | 김도훈 |
|    101 | 성연희 |
|    101 | 이만식 |
|    101 | 전은지 |
|    102 | 염일웅 |
|    102 | 권혁일 |
|    201 | 이재우 |
|    202 | 남은혁 |
+--------+--------+

• 학과 번호로 그룹을 형성하면 교수의 이름은 어떻게 처리해야 할까?

• GROUP BY 절에 명시되지 않은 컬럼은 반드시 집계함수를 사용해야한다.

예제 2

• 그룹 조회와 집계 함수의 관계 (2)
• GROUP BY 절을 사용하여 deptno 컬럼을 그룹화할 경우, name 컬럼에 대한 처리 기준이 정해지지 않았으므로 MySQL은 각 그룹 안에서 가장 첫 번째 이름을 출력한다.

SELECT deptno, name FROM professor GROUP BY deptno;

• 하지만 이는 일반적인 결과가 아니다.
• 다른 DBMS 들은 GROUP BY절에서 명시하지 않은 컬럼에 대한 처리 조건이 지정되지 않을 경우 에러를 발생시킨다.
• MySQL 8.X 버전 이후부터는 이 경우를 에러로 처리하기 시작했다.

예제 3

• 그룹 조회와 집계 함수의 관계 (3)

SELECT deptno, COUNT(name) FROM professor GROUP BY deptno;

+--------+-------------+
| deptno | COUNT(name) |
+--------+-------------+
|    101 |           4 |
|    102 |           2 |
|    201 |           1 |
|    202 |           1 |
+--------+-------------+

에러

• GROUP BY절에 명시되지 않은 컬럼을 SELECT에서 사용할 경우 집계 함수를 사용하여 각 그룹별 통계 데이터를 얻을 수 있다.
• GROUP BY절에 명시하지 않은 컬럼은 집계 함수로 처리한다.

예제 4

• 교수 테이블에서 학과별로 교수 수와 보직 수당을 받는 교수 수를 출력하시오

SELECT deptno, COUNT(*), COUNT(comm) FROM professor GROUP BY deptno ;

+--------+----------+-------------+
| deptno | COUNT(*) | COUNT(comm) |
+--------+----------+-------------+
|    101 |        4 |           2 |
|    102 |        2 |           1 |
|    201 |        1 |           0 |
|    202 |        1 |           1 |
+--------+----------+-------------+

• COUNT()에서 전체*값과 comm 컬럼값이 다른 이유는 컬럼은 null값 제외하기 때문이다.

📖 다중 컬럼을 이용한 그룹별 검색

• GROUP BY 절에서 두 개 이상의 컬럼을 콤마(,)로 구문하여 명시할 경우
• 첫 번째 컬럼에 대한 그룹을 형성하고,
• 각 그룹 안에서 두 번째 컬럼에 대한 2차 그룹을 형성한다.

SELECT {컬럼 [AS 별칭] ... , 그룹함수(컬럼)} FROM 테이블
[WHERE 검색조건]
[GROUP BY 컬럼1, 컬럼2, ..., 컬럼n]
[ORDER BY 컬럼 [정렬옵션]]

예제 1

• 학생 테이블에서 전체 학생을 소속 학과별로 나누고, 같은 학과 학생은 다시 학년별로 그룹핑하여, 학과와 학년별로 인원수, 평균 몸무게를 출력

SELECT deptno, grade, COUNT(*), AVG(weight) FROM student GROUP BY deptno, grade;

+--------+-------+----------+-------------+
| deptno | grade | COUNT(*) | AVG(weight) |
+--------+-------+----------+-------------+
|    101 |     4 |        2 |     82.0000 |
|    101 |     1 |        2 |     62.0000 |
|    101 |     3 |        1 |     88.0000 |
|    101 |     2 |        3 |     56.0000 |
|    102 |     2 |        1 |     48.0000 |
|    102 |     4 |        1 |     92.0000 |
|    102 |     1 |        1 |     68.0000 |
|    102 |     3 |        1 |     70.0000 |
|    201 |     1 |        3 |     65.3333 |
|    201 |     2 |        1 |     51.0000 |
+--------+-------+----------+-------------+

📘 집계 결과에 대한 조건지정

📖 HAVING

• SELECT 명령문의 WHERE 절과 비슷한 기능을 하는 것으로 GROUP BY절에서 조건 검색을 할 경우 반드시 HAVING절을 사용해야한다.

SELECT {컬럼 [AS 별칭] ... , 그룹함수(컬럼)} FROM 테이블
[WHERE 검색조건]
[GROUP BY 컬럼1, 컬럼2, ..., 컬럼n]
[HAVING 검색조선]
[ORDER BY 컬럼 [정렬옵션]]

예제 1

• Having절을 사용해야 하는 경우 이해하기 (1)
• 학생 수가 4명 초과인 학년에 대해서 학년, 학생 수, 평균 키, 평균 몸무게를 출력
• 단, 출력순서는 평균 키가 높은 순 부터 내림차순으로 출력
• GROUP BY 절만 사용할 경우 학생 수를 의미하는 COUNT(*)의 결과가 4이하인 데이터도 모두 조회된다.

SELECT grade, COUNT(*), AVG(height), AVG(weight) FROM student HAVING COUNT(*) > 4 ORDER BY height DESC;

에러. 별칭을 사용해야함

예제 2

• Having절을 사용해야 하는 경우 이해하기 (2)
• 집계 함수에 대한 검색 조건을 지정하고자 할 경우는 GROUP BY절 뒤에 HAVING절을 사용해야 한다.
• count(*) 의 결과가 4보다 큰 데이터만 조회되었다.

SELECT grade, COUNT(*), AVG(height) avg_height, AVG(weight) avg_weight FROM student GROUP BY grade HAVING COUNT(*) > 4 ORDER BY avg_height DESC;
+-------+----------+------------+------------+
| grade | COUNT(*) | avg_height | avg_weight |
+-------+----------+------------+------------+
|     1 |        6 |   175.3333 |    64.6667 |
|     2 |        5 |   164.8000 |    53.4000 |
+-------+----------+------------+------------+
2 rows in set (0.00 sec)

• 정렬조건에 SELECT에서 수행한 연산이나 집계 결과를 활용해야 할 경우

• SELECT절에서 별칭을 명시하고 이를 참조하면
  정렬 과정에서 발생하는 불필요한 추가 연산을
  방지할 수 있다.

• 주의 : AVG(height) DESC라고 쓰지 않는다.

예제 3

• 동일 학과 내에서 같은 학년에 재학중인 학생 수가 3명 이상인 그룹의 학과번호,학년,학생 수,최대 키,최대 몸무게를 출력하세요.

SELECT deptno, grade, COUNT(*), MAX(height), MAX(weight) FROM student GROUP BY deptno, grade HAVING COUNT(*) >= 3 ORDER BY deptno;
+--------+-------+----------+-------------+-------------+
| deptno | grade | COUNT(*) | MAX(height) | MAX(weight) |
+--------+-------+----------+-------------+-------------+
|    101 |     2 |        3 |         171 |          72 |
|    201 |     1 |        3 |         184 |          70 |
+--------+-------+----------+-------------+-------------+
2 rows in set (0.00 sec)

✏️ 연습문제

연습문제 1

• 학과별로 학과 번호, 교수들의 평균 급여, 최소 급여, 최대 급여를 출력

SELECT deptno, AVG(sal), MIN(sal), MAX(sal) FROM professor 
GROUP BY deptno;

+--------+----------+----------+----------+
| deptno | AVG(sal) | MIN(sal) | MAX(sal) |
+--------+----------+----------+----------+
|    101 | 372.5000 |      210 |      500 |
|    102 | 345.0000 |      240 |      450 |
|    201 | 320.0000 |      320 |      320 |
|    202 | 400.0000 |      400 |      400 |
+--------+----------+----------+----------+

연습문제 2

• 학과별로 학과번호, 평균 몸무게, 학생수를 출력하되, 평균 몸무게의 내림차순으로 정렬하세요

SELECT deptno, AVG(weight) avg_weight, COUNT(*) FROM student 
GROUP BY deptno 
ORDER BY avg_weight DESC;

+--------+------------+----------+
| deptno | avg_weight | COUNT(*) |
+--------+------------+----------+
|    102 |    69.5000 |        4 |
|    101 |    68.0000 |        8 |
|    201 |    61.7500 |        4 |
+--------+------------+----------+

연습문제 3

• 학과별로 교수 수가 2명 이하인 학과의 학과번호, 교수 수를 학과번호 순으로 정렬하여 출력

SELECT deptno, COUNT(*) FROM professor 
GROUP BY deptno 
HAVING COUNT(*) <= 2 
ORDER BY deptno;

+--------+----------+
| deptno | COUNT(*) |
+--------+----------+
|    202 |        1 |
|    201 |        1 |
|    102 |        2 |
+--------+----------+