MySQL 20번째수업

▶ GROUP BY와 HAVING

​​

1. 열의 그룹화​

​

예제 11-1) STUDENT 테이블에 있는 학생의 입학년도별 그룹을 출력하라.

A)

mysql> select substring(stu_no, 1, 4)

-> from student

-> group by substring(stu_no, 1, 4);

​

​

예제 11-2) 각 입학년도별 총 학생 수를 출력하라.

A)

mysql> select substring(stu_no, 1, 4), count(*)

-> from student

-> group by substring(stu_no, 1, 4);

​

​

예제 11-3) 등록한 학생에 대하여 학번, 등록횟수, 각 학생이 받은 장학금의 전체 합을 출력하라.

A)

mysql> select stu_no, count(*), sum(jang_total)

-> from fee

-> group by stu_no;

​

​

예제 11-4) '박정인' 학생에 대하여 학번, 등록한 횟수의 수를 출력하라.

A)

mysql> select stu_no, count(*)

-> from fee

-> where stu_no in

-> (select stu_no from student where stu_name = '박정인')

-> group by stu_no;

​

​

​

2. 2개 이상의 열에 대한 그룹화

​

​

예제 11-5) 학년별 주야 인원을 출력하라. (단, 출력 순서는 학년별 오름차순, 주야 오름차순이다)

A)

mysql> select grade, juya, count(*)

-> from student

-> group by grade, juya

-> order by grade, juya;

​

​

예제 11-6) STUDENT 테이블에서 학년, 반, 주야구분이 서로 다른 모든 조합을 인원수로 출력하라.

A)

mysql> select grade, class, juya, count(*)

-> from student

-> group by grade, class, juya;

​

​

예제 11-7) FEE 테이블에서 각 학생별로 대학 재학시 총 납입한 금액과 등록금 최대값, 가장 적게 받은 장학금, 등록 횟수를 출력하라.

A)

mysql> select stu_no, sum(fee_pay), max(fee_total),

-> min(jang_total), count(*)

-> from fee

-> group by stu_no;

​

​

예제 11-8) 등록한 학생에 대하여 학번, 이름, 납입금의 총액을 출력하라.

A)

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;

​

​

​

3. 수식의 그룹화

​

​

예제 11-9) 등록 연도에 대하여 등록된 수를 출력하라.

A)

mysql> select fee_year, count(*)

-> from fee

-> group by fee_year;

​

​

예제 11-10) 동아리 가입번호를 기초로 하여 학생들을 그룹화하라. 이 때 그룹 1은 가입번호 1부터 3까지이며, 그룹 2는 가입번호 4부터 6까지의 순서로 3명씩을 한 그룹으로 그룹화한다. 그리고 각 그룹에 대하여 학생의 수와 가장 높은 학번을 출력한다.

A)

mysql> select ceil(cir_num/3), count(*), max(stu_no)

-> from circle

-> group by ceil(cir_num/3);

​

​

​

4. NULL값의 그룹화

​

​

예제 11-11) 서로 다른 장학코드를 그룹화하고 인원수를 출력하라.​

A)

mysql> select ifnull(jang_code, null) "장학코드", count(*) ← ifnull 안 써도 됨.

-> from fee

-> group by jang_code;

​

​

​

5. HAVING 절

​

​

예제 11-12) 세 번 이상 등록한 학생의 학번과 등록 횟수를 출력하라.

A)

mysql> select stu_no, count(*)

-> from fee

-> group by stu_no

-> having count(*) > 2;

​

​

예제 11-13) 2019년에 등록한 학생의 학번과 등록 횟수를 출력하라.​

A)

mysql> select stu_no, fee_year, count(*)

-> from fee

-> group by stu_no, fee_year

-> having fee_year = '2019';

​

​​

예제 11-14) 재학중에 납부한 등록금의 전체 납부금액이 5,000,000원 이상인 각 학생에 대하여 출력하라.

A)

mysql> select stu_no, sum(fee_pay)

-> from fee

-> group by stu_no

-> having sum(fee_pay) >= 5000000;

​

​

예제 11-15) 남학생이면서 재학중 납부한 전체등록금이 2,000,000원 이상인 학생의 학번과 등록금의 총액을 출력하라.​

A)

mysql> select stu_no, sum(fee_pay)

-> from fee

-> where stu_no in

-> (select stu_no from student where gender in (1, 3, 5))

-> group by stu_no

-> having sum(fee_pay) >= 2000000;

​

​

예제 11-16) 재학중 납부한 등록금 총액이 가장 많은 각 학생에 대한 학번과 등록금의 총액을 출력하라. (만약 등록금 총액이 모두 같거나 모두 많다면, 이 질의어는 많은 학생을 반환할 것이다)

A)

mysql> select stu_no, sum(fee_pay)

-> from fee

-> group by stu_no

-> having sum(fee_pay) >= all

-> (select sum(fee_pay) from fee group by stu_no);

​

★GROUP BY를 사용하면 DISTINCT를 쓸 필요가 없다.

​

​

<연습문제>

11-2) STUDENT 테이블로부터 서로 다른 출생 연도와 인원을 출력하라.​

A)

mysql> select substring(birthday, 1, 4), count(*)

-> from student

-> group by substring(birthday, 1, 4);

​

​

11-3) 적어도 한 번 이상 장학금을 지급받은 학생의 학번과 장학금총액, 장학금 평균값, 장학금 지급 받은 횟수를 출력하라.

A)

mysql> select stu_no, sum(ifnull(jang_total, 0)), avg(ifnull(jang_total, 0)), count(*)

-> from fee

-> group by stu_no

-> having count(*) >= 1;

​

mysql> select stu_no, sum(jang_total), avg(jang_total), count(jang_total)

-> from fee

-> group by stu_no

-> having count(jang_total) >= 1;

​

​

11-4) 교과목 테이블에서 과목이 생성된 연도로 그룹화하여 과목 수를 출력하라.

A)

mysql> select create_year, count(sub_code)

-> from subject

-> group by create_year;

​

​

11-5) 교수 테이블에서 교수가 대학에 발령받은 연도로 그룹화하여 교수 인원을 출력하라.

A)

mysql> select year(create_date), count(create_date)

-> from professor

-> group by year(create_date)

-> order by year(create_date);

​

​

11-6) 경기도 지역에 거주하는 학생의 학번과 이름, 우편번호, 주소를 출력하라.

A)

mysql> select stu_no, stu_name, post_no, address

-> from student

-> where substring(address, 1, 3) = '경기도';

​

​

11-7) 납부한 등록금 총액이 5,000,000원 미만인 학생의 학번과 등록금 총액을 출력하라.

A)

mysql> select stu_no, sum(fee_pay)

-> from fee

-> group by stu_no

-> having sum(fee_pay) < 5000000;

​

​

11-8) 수강신청 과목이 2과목 이상인 학생의 학번, 이름, 수강신청 과목 수를 출력하라.

A)

mysql> select a.stu_no, stu_name, count(sub_code)

-> from student s, attend a

-> where s.stu_no = a.stu_no

-> group by a.stu_no

-> having count(sub_code) >= 2;

​

​

11-9) 장학금을 가장 많이 받은 학생의 학번, 이름, 장학금 총액을 출력하라.​

A)

mysql> select f.stu_no, stu_name, sum(jang_total)

-> from student s, fee f

-> where s.stu_no = f.stu_no

-> group by f.stu_no

-> having sum(jang_total) >= all

-> (select sum(jang_total) from fee group by stu_no);