11장. GROUP BY와 HAVING 열의 그룹화 2개 이상의 열에 대한 그룹화

Presentation on theme: "11장. GROUP BY와 HAVING 열의 그룹화 2개 이상의 열에 대한 그룹화"— Presentation transcript:

1 11장. GROUP BY와 HAVING 열의 그룹화 2개 이상의 열에 대한 그룹화
3. 수식의 그룹화 NULL 값의 그룹화 5. GROUP BY와 DISTINCT 6. HAVING 절의 소개 7. HAVING 절의 예제 8. HAVING 절에 대한 일반적인 규칙 쉽게 배우는 MySQL 5.x

2 11.1 열의 그룹화 -GROUP BY 절은 동일성을 기초하여 여러 개의 행을 그룹화
-HAVING 절은 WHERE 절과 유사한 기능을 가지며, 그룹의 조건을 지정 11.1 열의 그룹화 [예제 11-1] STUDENT 테이블에 있는 학생의 입학년도별 그룹을 출력하라. mysql> select substring(stu_no, 1, 4)     -> from student     -> group by substring(stu_no, 1, 4); | substring(stu_no, 1, 4) | | 2000                    | | 2004                    | | 2006                    | | 2007                    | 4 rows in set (0.00 sec) GROUP BY 절을 사용하지 않고, DISTINCT를 사용하면 동일한 결과 출력 mysql> select distinct(substring(stu_no, 1, 4))    -> from student; 쉽게 배우는 MySQL 5.x

3 [예제 11-1] GROUP BY절을 사용하여 생성된 중간결과
substring(stu_no, 1, 4) stu_no                      stu_name ----                        2000                   , ,     김유신, 박도준, 이상길 2004                   , ,     김유미, 정인정, 연개소문 2006                   , ,     박정인, 고혜진, 김영호  2007                   , ,     장수인, 홍길동, 이순신                         , ,     유하나, 김문영, 최차영 -동일한 년도를 가지고 있는 모든 행은 그룹으로 구성 -중간 결과에 있는 각 행에 대하여 년도 열은 하나의 값 -다른 모든 열은 여러 개의 값(STU_NO, STU_NAME은 여러 개의 값) 최종 결과 값은 | substring(stu_no, 1, 4) | | 2000                    | | 2004                    | | 2006                    | | 2007                    | 쉽게 배우는 MySQL 5.x

4 [예제 11-2] 각 입학년도별 총 학생 수를 출력하라.
mysql> select substring(stu_no, 1, 4), count(*)     -> from student     -> group by substring(stu_no, 1, 4); | substring(stu_no, 1,4) | count(*) | | 2000                   |        3 | | 2004                   |        3 | | 2006                   |        3 | | 2007                   |        6 | 4 rows in set (0.02 sec) COUNT(*) 함수는 서로 다른 모든 행에 대하여 연산하지 않고 각 그룹화 된 행에 의존하여 실행된다. 다시 말하면, COUNT(*) 함수는 각 그룹화된 행에서 계산된다. 쉽게 배우는 MySQL 5.x

5 [예제11-3] 등록한 학생에 대하여 학번, 등록횟수, 각 학생이 받은 장학금의 전체 합을 출력하라.
mysql> select stu_no, count(*), sum(jang_total)     -> from fee     -> group by stu_no; | stu_no   | count(*) | sum(jang_total) | | |        8 |        | | |        4 |         | | |        2 |          | | |        2 |          | | |        2 |          | | |        2 |         | | |        2 |          | | |        2 |         | 8 rows in set (0.02 sec) 쉽게 배우는 MySQL 5.x

6 [예제11-4] 박정인 학생에 대하여 학번, 등록한 횟수의 수를 출력하라.
mysql> select stu_no, count(*)     -> from fee     -> where stu_no in     -> (select stu_no     -> from student     -> where stu_name = '박정인')     -> group by stu_no; | stu_no   | count(*) | | |        4 | 1 row in set (0.00 sec) 쉽게 배우는 MySQL 5.x

7 11.2 2개 이상의 열에 대한 그룹화 GROUP BY 절에 2개 이상의 열 명세를 사용할 수 있다.
개 이상의 열에 대한 그룹화 GROUP BY 절에 2개 이상의 열 명세를 사용할 수 있다. [예제11-5] 학년별, 주야인원을 출력하라. 단, 출력 순서는 학년별 오름차순, 주야 오름차순이다. mysql> select  grade, juya, count(*)     -> from student     -> group by grade, juya     -> order by grade, juya; | grade | juya | count(*) | |     1 | 주   |        3 | |     1 | 야   |        3 | |     2 | 주   |        3 | |     3 | 주   |        1 | |     3 | 야   |        1 | |     4 | 주   |        2 | |     4 | 야   |        2 | 7 rows in set (0.00 sec) 쉽게 배우는 MySQL 5.x

8 [예제 11-5] GROUP BY절의 중간결과는 학년별 주야구분별로 학번을 그룹화
  grade   juya   stu_no          1   주     , ,       1   야     , ,       2   주     , ,       3   주           3   야           4   주     ,       4   야     , 최종 결과 값은 | grade | juya | count(*) | |     1 | 주   |        3 | |     1 | 야   |        3 | |     2 | 주   |        3 | |     3 | 주   |        1 | |     3 | 야   |        1 | |     4 | 주   |        2 | |     4 | 야   |        2 | 7 rows in set (0.00 sec) 쉽게 배우는 MySQL 5.x

9 [예제11-6]STUDENT 테이블에서 학년, 반, 주야구분이 서로 다른 모든 조합을 인원수로 출력하라.
mysql> select grade, class, juya, count(*) mysql> select class, grade, juya, count(*)     -> from student > from student     -> group by grade, class, juya; > group by class, grade, juya; | grade | class | juya | count(*) | | class | grade | juya | count(*) | |     1 |     1 | 주   |        2 | |     1 |     1 | 주   |        2 | |     1 |     2 | 주   |        1 | |     1 |     2 | 주   |        2 | |     1 |     3 | 야   |        3 | |     1 |     4 | 주   |        2 | |     2 |     1 | 주   |        2 | |     2 |     1 | 주   |        1 | |     2 |     2 | 주   |        1 | |     2 |     2 | 주   |        1 | |     3 |     2 | 주   |        1 | |     2 |     3 | 주   |        1 | |     3 |     3 | 야   |        1 | |     3 |     1 | 야   |        3 | |     4 |     1 | 주   |        2 | |     3 |     3 | 야   |        1 | |     4 |     3 | 야   |        2 | |     3 |     4 | 야   |        2 | 9 rows in set (0.00 sec) rows in set (0.00 sec) 예제에서 우측과 같이 학년과 반을 서로 바꾸어 보자. 출력되는 순서가 반을 기준으로 오름차순으로 출력될 뿐 결과값은 동일하게 출력된다. 쉽게 배우는 MySQL 5.x

10 [예제11-7]FEE 테이블에서 각 학생별로 대학 재학시 총 납입한 금액과  등록금 최대값, 가장 적게 받은 장학금, 등록 횟수를 출력하라.
mysql> select stu_no, sum(fee_pay), max(fee_total),     -> min(jang_total), count(*)     -> from fee     -> group by stu_no; | stu_no   | sum(fee_pay) | max(fee_total) | min(jang_total) | count(*) | | |      |        |         |        8 | | |      |        |          |        4 | | |      |        |          |        2 | | |      |        |          |        2 | | |      |        |          |        2 | | |      |        |          |        2 | | |      |        |          |        2 | | |      |        |          |        2 | 8 rows in set (0.16 sec) 쉽게 배우는 MySQL 5.x

11 [예제11-8] 등록한 학생에 대하여 학번, 이름, 납입금의 총액을 출력하라.
mysql> select s.stu_no, stu_name, sum(fee_pay)     -> from student s, fee f     -> where s.stu_no = f.stu_no     -> group by s.stu_no, stu_name; | stu_no   | stu_name | sum(fee_pay) | | | 박도준   |      | | | 박정인   |      | | | 장수인   |      | | | 홍길동   |      | | | 이순신   |      | | | 유하나   |      | | | 김문영   |      | | | 최차영   |      | 8 rows in set (0.05 sec) 쉽게 배우는 MySQL 5.x

12 11.3 수식의 그룹화 SQL 제품은 수식을 그룹화 할 수 있는 기능을 제공
11.3 수식의 그룹화 SQL 제품은 수식을 그룹화 할 수 있는 기능을 제공 [예제 11-9] 등록 연도에 대하여 등록된 수를 출력하라. mysql> select fee_year, count(*)     -> from fee     -> group by fee_year; | fee_year | count(*) | | 2000     |        2 | | 2001     |        2 | | 2006     |        4 | | 2007     |       16 | 4 rows in set (0.00 sec) 쉽게 배우는 MySQL 5.x

13 Ceil( ) 함수는 소수 이하 자리가 0.5 이하도 무조건 올림을 수행
[예제 11-10] 동아리 가입번호를 기초로 하여 학생들을 그룹화하라. 이 때 그룹 1은 가입번호 1부터 3까지이며, 그룹 2는 가입번호 4부터 6까지의 순서로 3명씩을 한 그룹으로 그룹화한다. 그리고 각 그룹에 대하여 학생의 수와 가장 높은 학번을 출력한다. mysql> select ceil(cir_num/3), count(*), max(stu_no)     -> from circle     -> group by ceil(cir_num/3); | ceil(cir_num/3) | count(*) | max(stu_no) | |               1 |        3 |     | |               2 |        3 |     | |               3 |        1 |     | 3 rows in set (0.06 sec) Ceil( ) 함수는 소수 이하 자리가 0.5 이하도 무조건 올림을 수행 쉽게 배우는 MySQL 5.x

14 11.4 NULL 값의 그룹화 NULL 값을 가지고 있는 열을 그룹화한다면 NULL 값은 하나의 그룹으로 구성
[예제 11-11] 서로 다른 장학코드를 그룹화하고 인원수를 출력하라. mysql> select ifnull(jang_code,null) "장학코드", count(*)     -> from fee     -> group by jang_code; | 장학코드 | count(*) | | NULL     |        4 | | 1        |        7 | | 10       |        8 | | 11       |        3 | | 2        |        1 | | 21       |        1 | 6 rows in set (0.00 sec) 쉽게 배우는 MySQL 5.x

15 11.5 GROUP BY와 DISTINCT 11.6 HAVING절의 소개
• SELECT 절이 GROUP BY 절에서 지정한 모든 열을 가지고 있다면 DISTINCT(통계 함수의 외부에 사용될 때)는 필요하지 않다. • GROUP BY 절은 열이 중복된 값을 갖지 않도록 행을 그룹화하기 때문이다. 11.6 HAVING절의 소개 • GROUP BY 절은 FROM 절로부터 생성되는 결과의 행을 그룹화 • HAVING 절은 특별한 그룹 속성을 기초로 하여 행의 그룹을 선택 • HAVING 절에 있는 조건은 WHERE 절에 있는 일반적인 조건처럼 보인다. • WHERE 절에 있는 조건 수식에서는 통계 함수를 사용 불가능 • 부속 질의어에서는 통계 함수를 사용 가능 • HAVING 절의 조건에 있는 수식은 통계 함수를 사용 가능 쉽게 배우는 MySQL 5.x

16 [예제 11-12] 세 번 이상 등록한 학생의 학번과 등록 횟수를 출력하라.
mysql> select stu_no, count(*)     -> from fee     -> group by stu_no     -> having count(*) > 2; | stu_no   | count(*) | | |        8 | | |        4 | 2 rows in set (0.00 sec) GROUP BY 절의 중간 결과 | stu_no   | fee_year | fee_term | | | 2000     |     1, 2 | |          | 2001     |     1, 2 | |          | 2006     |     1, 2 | |          | 2007     |     1, 2 | | | 2006     |     1, 2 | | | 2007     |     1, 2 | | | 2007     |     1, 2 | | | 2007     |     1, 2 | | | 2007     |     1, 2 | | | 2007     |     1, 2 | | | 2007     |     1, 2 | 쉽게 배우는 MySQL 5.x

17 11.7 HAVING 절의 예제 [예제 11-13] 2006년에 등록한 학생의 학번과 등록 횟수를 출력하라.
mysql> select stu_no, fee_year, count(*)     -> from fee     -> group by stu_no, fee_year     -> having fee_year = '2006'; | stu_no   | fee_year | count(*) | | | 2006     |        2 | | | 2006     |        2 | 2 rows in set (0.00 sec) 쉽게 배우는 MySQL 5.x

18 [예제11-14]재학중에 납부한 등록금의 전체 납부금액이 5,000,000원 이상인 각 학생에 대하여 출력하라.
mysql> select stu_no, sum(fee_pay)     -> from fee     -> group by stu_no     -> having sum(fee_pay) >= ; | stu_no   | sum(fee_pay) | | |      | | |      | | |      | | |      | | |      | | |      | 6 rows in set (0.00 sec) 쉽게 배우는 MySQL 5.x

19 [예제 11-15] 여학생이면서 재학중 납부한 전체등록금이 2,000,000원 이상인 학생의 학번과 등록금의 총액을 출력하라.
mysql> select stu_no, sum(fee_pay)     -> from fee     -> where stu_no in     -> (select stu_no     -> from student     -> where substring(id_num, 8, 1) =2)     -> group by stu_no     -> having sum(fee_pay) >= ; | stu_no   | sum(fee_pay) | | |      | | |      | | |      | 3 rows in set (0.03 sec) 쉽게 배우는 MySQL 5.x

20 [예제 11-16] 재학중 납부한 등록금 총액이 가장 많은 각 학생에 대한 학번과 등록금의 총액을 출력하라(만약 등록금 총액이 모두 같거나 모두 많다면, 이 질의어는 많은 학생을 반환할 것이다). mysql> select stu_no, sum(fee_pay)     -> from fee     -> group by stu_no     -> having sum(fee_pay) >= all     -> (select sum(fee_pay)     -> group by stu_no); | stu_no   | sum(fee_pay) | | |      | 1 row in set (0.00 sec) 쉽게 배우는 MySQL 5.x

21 11.8 HAVING 절에 대한 일반적인 규칙 -HAVING 절에서 사용된 각 열의 이름은 통계 함수 내부에서 사용하거나
GROUP BY 절의 열의 리스트에서 사용되어야 한다. -통계 함수의 결과는 항상 각 그룹에 대하여 하나의 값으로 구성 -그룹화되는 열 명세의 결과는 그룹 당 하나의 값으로 구성 -그룹화가 되지 않는 열의 명세의 결과는 값들의 집합으로 구성 mysql> select birth_year, count(*)     -> from student     -> group by birth_year     -> having class = 2; ERROR 1054 (42S22): Unknown column 'class' in 'having clause' CLASS 열이 통계 함수 내부에서도 그룹화를 수행하는 열의 리스트에서도 사용되지 않고 HAVING 절에서 사용되기 때문 쉽게 배우는 MySQL 5.x