CH15-06: 집계함수 문제풀이
살인의 평균 발생 건수를 검색하고 확인하기
mysql> use zerobase;
Database changed
mysql> select avg(case_number)
-> from crime_status
-> where status_type = '발생'
-> and crime_type = '살인';
+
| avg(case_number) |
+
| 4.5484 |
+
1 row in set (0.02 sec)
서초경찰서의 범죄 별 평균 검거건수
mysql> select avg(case_number)
-> from crime_status
-> where police_station like '서초' and status_type like '검거';
+
| avg(case_number) |
+
| 495.2000 |
+
1 row in set (0.01 sec)
구로경찰서와 도봉경찰서의 평균 살인 검거 건수 검색
mysql> select avg(case_number)
-> from crime_status
-> where police_station in ('도봉','구로')
-> and crime_type like '살인' and status_type like '검거';
+
| avg(case_number) |
+
| 3.0000 |
+
1 row in set (0.01 sec)
광진경찰서에서 가장 낮은 범죄 검거 건수를 검색 후 확인하기
mysql> select min(case_number)
-> from crime_status
-> where police_station like '%광진%'
-> and status_type like '%검거%';
+
| min(case_number) |
+
| 2 |
+
1 row in set (0.01 sec)
mysql> select * from crime_status
-> where police_station like '광진'
-> and status_type like '검거';
+
| year | police_station | crime_type | status_type | case_number |
+
| 2020 | 광진 | 살인 | 검거 | 2 |
| 2020 | 광진 | 강도 | 검거 | 4 |
| 2020 | 광진 | 강간,추행 | 검거 | 224 |
| 2020 | 광진 | 절도 | 검거 | 874 |
| 2020 | 광진 | 폭력 | 검거 | 1407 |
+
5 rows in set (0.01 sec)
성북 경찰서에서 가장 낮은 범죄 발생 건수를 검색하고 확인하기
mysql> select min(case_number)
-> from crime_status
-> where police_station like '%성북%'
-> and status_type like '%발생%';
+
| min(case_number) |
+
| 0 |
+
1 row in set (0.01 sec)
mysql> select * from crime_status
-> where police_station like '%성북%'
-> and status_type like '%발생%';
+
| year | police_station | crime_type | status_type | case_number |
+
| 2020 | 성북 | 살인 | 발생 | 0 |
| 2020 | 성북 | 강도 | 발생 | 2 |
| 2020 | 성북 | 강간 | 발생 | 95 |
| 2020 | 성북 | 절도 | 발생 | 439 |
| 2020 | 성북 | 폭력 | 발생 | 672 |
+
5 rows in set (0.01 sec)
영등포 경찰서의 가장 높은 범죄 발생 건수 검색 확인
mysql> select max(case_number)
-> from crime_status
-> where police_station like '%영등포%'
-> and status_type like '%발생%';
+
| max(case_number) |
+
| 2701 |
+
1 row in set (0.01 sec)
mysql> select * from crime_status
-> where police_station like '%영등포%'
-> and status_type like '%발생%';
+
| year | police_station | crime_type | status_type | case_number |
+
| 2020 | 영등포 | 살인 | 발생 | 7 |
| 2020 | 영등포 | 강도 | 발생 | 6 |
| 2020 | 영등포 | 강간,추행 | 발생 | 315 |
| 2020 | 영등포 | 절도 | 발생 | 2188 |
| 2020 | 영등포 | 폭력 | 발생 | 2701 |
+
5 rows in set (0.01 sec)
절도 검거가 가장 많은 건수 검색 확인
mysql> select max(case_number)
-> from crime_status
-> where crime_type like '%절도%'
-> and status_type like '%검거%';
+
| max(case_number) |
+
| 1137 |
+
1 row in set (0.01 sec)
mysql> select * from crime_status
-> where crime_type like '%절도%'
-> and status_type like '%검거%';
+
| year | police_station | crime_type | status_type | case_number |
+
| 2020 | 중부 | 절도 | 검거 | 495 |
| 2020 | 종로 | 절도 | 검거 | 284 |
| 2020 | 남대문 | 절도 | 검거 | 368 |
| 2020 | 서대문 | 절도 | 검거 | 524 |
| 2020 | 혜화 | 절도 | 검거 | 310 |
| 2020 | 용산 | 절도 | 검거 | 534 |
| 2020 | 성북 | 절도 | 검거 | 233 |
| 2020 | 동대문 | 절도 | 검거 | 713 |
| 2020 | 마포 | 절도 | 검거 | 679 |
| 2020 | 영등포 | 절도 | 검거 | 953 |
| 2020 | 성동 | 절도 | 검거 | 565 |
| 2020 | 동작 | 절도 | 검거 | 592 |
| 2020 | 광진 | 절도 | 검거 | 874 |
| 2020 | 서부 | 절도 | 검거 | 322 |
| 2020 | 강북 | 절도 | 검거 | 490 |
| 2020 | 금천 | 절도 | 검거 | 553 |
| 2020 | 중랑 | 절도 | 검거 | 889 |
| 2020 | 강남 | 절도 | 검거 | 1016 |
| 2020 | 관악 | 절도 | 검거 | 1137 |
| 2020 | 강서 | 절도 | 검거 | 1070 |
| 2020 | 강동 | 절도 | 검거 | 781 |
| 2020 | 종암 | 절도 | 검거 | 347 |
| 2020 | 구로 | 절도 | 검거 | 1008 |
| 2020 | 서초 | 절도 | 검거 | 801 |
| 2020 | 양천 | 절도 | 검거 | 718 |
| 2020 | 송파 | 절도 | 검거 | 1006 |
| 2020 | 노원 | 절도 | 검거 | 646 |
| 2020 | 방배 | 절도 | 검거 | 159 |
| 2020 | 은평 | 절도 | 검거 | 434 |
| 2020 | 도봉 | 절도 | 검거 | 379 |
| 2020 | 수서 | 절도 | 검거 | 475 |
+
31 rows in set (0.01 sec)
CH15-07: Group by
Group by 문법
SELECT COL1, COL2,...
FROM TABLENAME
WHERE COND
GROUP BY COL1, COL2,...
ORDER BY COL1, COL2,...
CRIME_STATUS에서 경찰서별로 그룹화 하여 경찰서 이름을 조회 오름차순으로 5개까지만 조회
mysql> SELECT POLICE_STATION
-> FROM crime_status
-> GROUP BY POLICE_STATION
-> ORDER BY POLICE_STATION
-> LIMIT 5;
+
| POLICE_STATION |
+
| 강남 |
| 강동 |
| 강북 |
| 강서 |
| 관악 |
+
5 rows in set (0.01 sec)
DISTINCT와 GROUP BY 같이 쓸 수는 있는데 ORDER BY까지는 같이 못 씀 (정렬을 못함)
경찰서 별 총 범죄 발생 건수 검색 (오름차순으로 5개까지 )
mysql> SELECT POLICE_STATION, STATUS_TYPE, SUM(CASE_NUMBER)
-> FROM crime_status
-> WHERE STATUS_TYPE = '발생'
-> GROUP BY POLICE_STATION
-> ORDER BY SUM(CASE_NUMBER)
-> LIMIT 5;
+
| POLICE_STATION | STATUS_TYPE | SUM(CASE_NUMBER) |
+
| 방배 | 발생 | 836 |
| 성북 | 발생 | 1208 |
| 남대문 | 발생 | 1354 |
| 종암 | 발생 | 1359 |
| 서부 | 발생 | 1362 |
+
5 rows in set (0.01 sec)
경찰서 별 평균 범죄 검거 건수를 검색(내림차순으로 5개까지)
mysql> SELECT POLICE_STATION, STATUS_TYPE, AVG(CASE_NUMBER)
-> FROM crime_status
-> WHERE STATUS_TYPE LIKE '%검거%'
-> GROUP BY POLICE_STATION
-> ORDER BY 3 DESC
-> LIMIT 5;
+
| POLICE_STATION | STATUS_TYPE | AVG(CASE_NUMBER) |
+
| 관악 | 검거 | 771.6000 |
| 송파 | 검거 | 708.8000 |
| 강서 | 검거 | 678.6000 |
| 강남 | 검거 | 674.6000 |
| 영등포 | 검거 | 674.2000 |
+
5 rows in set (0.01 sec)
경찰서별 평균 범죄 발생 건수와 평균 범죄 검거 건수를 검색 (6개까지)
mysql> SELECT POLICE_STATION, STATUS_TYPE, AVG(CASE_NUMBER)
-> FROM crime_status
-> GROUP BY POLICE_STATION, STATUS_TYPE
-> LIMIT 6;
+
| POLICE_STATION | STATUS_TYPE | AVG(CASE_NUMBER) |
+
| 중부 | 발생 | 411.4000 |
| 중부 | 검거 | 281.2000 |
| 종로 | 발생 | 338.8000 |
| 종로 | 검거 | 235.8000 |
| 남대문 | 발생 | 270.8000 |
| 남대문 | 검거 | 183.4000 |
+
6 rows in set (0.01 sec)
CH15-08~10: HAVING
조건에 집계함수가 포함되는 경우 WHERE 대신 HAVING 사용
SELECT COL1, COL2, ...
FROM TABLE
WHERE COND
GROUP BY COL1, COL2,...
HAVING COND (집계함수)
ORDER BY COL1, COL2,...
경찰서 별로 발생한 범죄 건수의 합이 4000건 초과인 경우
mysql> SELECT POLICE_STATION, STATUS_TYPE, SUM(CASE_NUMBER) AS 발생건수
-> FROM crime_status
-> WHERE STATUS_TYPE LIKE '%발생%'
-> GROUP BY POLICE_STATION
-> HAVING 발생건수 > 4000;
+
| POLICE_STATION | STATUS_TYPE | 발생건수 |
+
| 영등포 | 발생 | 5217 |
| 강남 | 발생 | 4754 |
| 관악 | 발생 | 5261 |
| 강서 | 발생 | 4415 |
| 구로 | 발생 | 4175 |
| 송파 | 발생 | 5410 |
+
6 rows in set (0.01 sec)
경찰서 별로 발생한 폭력과 절도의 범죄 건수 평균이 2000이상인 경우
mysql> SELECT POLICE_STATION, STATUS_TYPE, AVG(CASE_NUMBER)
-> FROM crime_status
-> WHERE STATUS_TYPE LIKE '발생'
-> AND (CRIME_TYPE LIKE '%폭력%' OR CRIME_TYPE LIKE '%절도%')
-> GROUP BY POLICE_STATION
-> HAVING AVG(CASE_NUMBER) >= 2000;
+
| POLICE_STATION | STATUS_TYPE | AVG(CASE_NUMBER) |
+
| 영등포 | 발생 | 2444.5000 |
| 강남 | 발생 | 2112.0000 |
| 관악 | 발생 | 2421.5000 |
| 강서 | 발생 | 2067.0000 |
| 송파 | 발생 | 2552.0000 |
+
5 rows in set (0.01 sec)
mysql> SELECT * FROM crime_station
-> where police_station like '영등포';
ERROR 1146 (42S02): Table 'zerobase.crime_station' doesn't exist
mysql> SELECT * FROM crime_status
-> WHERE POLICE_STATION LIKE '%영등포%';
+
| year | police_station | crime_type | status_type | case_number |
+
| 2020 | 영등포 | 살인 | 발생 | 7 |
| 2020 | 영등포 | 살인 | 검거 | 4 |
| 2020 | 영등포 | 강도 | 발생 | 6 |
| 2020 | 영등포 | 강도 | 검거 | 5 |
| 2020 | 영등포 | 강간,추행 | 발생 | 315 |
| 2020 | 영등포 | 강간,추행 | 검거 | 252 |
| 2020 | 영등포 | 절도 | 발생 | 2188 |
| 2020 | 영등포 | 절도 | 검거 | 953 |
| 2020 | 영등포 | 폭력 | 발생 | 2701 |
| 2020 | 영등포 | 폭력 | 검거 | 2157 |
+
10 rows in set (0.01 sec)
CH15-11: GROUP BY, HAVING 문제풀이
경찰서별로 절도 범죄 평균 발생 건수를 가장 많은 건수 순으로 10개 검색후 확인
mysql> SELECT POLICE_STATION, STATUS_TYPE, AVG(CASE_NUMBER) AS 절도평균발생
-> FROM crime_status
-> WHERE STATUS_TYPE LIKE '%발생%' AND CRIME_TYPE LIKE '%절도%'
-> GROUP BY POLICE_STATION
-> ORDER BY 3 DESC
-> LIMIT 10;
+
| POLICE_STATION | STATUS_TYPE | 절도평균발생 |
+
| 송파 | 발생 | 2429.0000 |
| 관악 | 발생 | 2229.0000 |
| 영등포 | 발생 | 2188.0000 |
| 강남 | 발생 | 1941.0000 |
| 구로 | 발생 | 1763.0000 |
| 강서 | 발생 | 1689.0000 |
| 서초 | 발생 | 1673.0000 |
| 광진 | 발생 | 1664.0000 |
| 강동 | 발생 | 1640.0000 |
| 양천 | 발생 | 1517.0000 |
+
10 rows in set (0.01 sec)
경찰서 별로 가장 많이 검거한 범죄 건수를 가장 적은 건수 순으로 5개 검색
mysql> SELECT POLICE_STATION, CRIME_TYPE, STATUS_TYPE, MAX(CASE_NUMBER)
-> FROM crime_status
-> WHERE STATUS_TYPE LIKE '%검거%'
-> GROUP BY POLICE_STATION
-> ORDER BY 4 ASC
-> LIMIT 5;
+
| POLICE_STATION | CRIME_TYPE | STATUS_TYPE | MAX(CASE_NUMBER) |
+
| 방배 | 살인 | 검거 | 365 |
| 남대문 | 살인 | 검거 | 494 |
| 성북 | 살인 | 검거 | 610 |
| 혜화 | 살인 | 검거 | 628 |
| 종암 | 살인 | 검거 | 630 |
+
5 rows in set (0.01 sec)
경찰서 별 가장 적게 검거한 건수 중 4건 보다 큰 경우를 건수가 큰 순으로 정렬하여 검색하시오
mysql> SELECT POLICE_STATION, CRIME_TYPE, STATUS_TYPE, MIN(CASE_NUMBER) AS 최소검거건수
-> FROM crime_status
-> WHERE STATUS_TYPE LIKE '검거'
-> GROUP BY POLICE_STATION
-> HAVING 최소검거건수 > 4
-> ORDER BY 최소검거건수 DESC;
+
| POLICE_STATION | CRIME_TYPE | STATUS_TYPE | 최소검거건수 |
+
| 중랑 | 살인 | 검거 | 6 |
| 송파 | 살인 | 검거 | 6 |
| 동대문 | 살인 | 검거 | 5 |
| 강서 | 살인 | 검거 | 5 |
| 구로 | 살인 | 검거 | 5 |
+
5 rows in set (0.01 sec)
대문으로 끝나는 이름의 경찰서 별 범죄 발생 건수의 평균이 500건 이상인 경우를 검색
mysql> SELECT POLICE_STATION, STATUS_TYPE, AVG(CASE_NUMBER) AS 평균발생건수
-> FROM crime_status
-> WHERE POLICE_STATION LIKE '%대문'
-> AND STATUS_TYPE LIKE '%발생%'
-> GROUP BY POLICE_STATION
-> HAVING 평균발생건수 >= 500;
+
| POLICE_STATION | STATUS_TYPE | 평균발생건수 |
+
| 서대문 | 발생 | 506.6000 |
| 동대문 | 발생 | 680.2000 |
+
2 rows in set (0.01 sec)
