SQL - 집계함수

⏹ Aggregate Funtions (집계함수)

:여러 컬럼 혹은 테이블 전체 컬럼으로부터 하나의 결과값을 반환하는 함수.

Funtion	Description
COUNT	총 갯수를 계산해주는 함수
SUM	합계를 계산해주는 함수
AVG	평균을 계산해주는 함수
MIN	가장 작은 값을 찾아주는 함수
MAX	가장 큰 값을 찾아주는 함수
FIRST	첫번째 결과값을 리턴하는 함수
LAST	마지막 결과 값을 리턴하는 함수

◼ COUNT

SELECT COUNT(column) FROM tablename WHERE 조건;
: 컬럼의 총 개수 반환

❔ police_station 테이블의 총 데이터 개수?

mysql> select count(*) from police_station;
+----------+
| count(*) |
+----------+
|       31 |
+----------+
1 row in set (0.05 sec)

아래 방법을 count함수를 사용하면 총 개수 바로 확인 가능.

mysql> select distinct police_station from crime_status;
+----------------+
| police_station |
+----------------+
| 중부           |
| 종로           |
| 남대문         |
| 서대문         |
| 혜화           |
...
| 은평           |
| 도봉           |
| 수서           |
+----------------+
31 rows in set (0.04 sec)

⬇

mysql> select count(distinct police_station) from crime_status;
+--------------------------------+
| count(distinct police_station) |
+--------------------------------+
|                             31 |
+--------------------------------+
1 row in set (0.04 sec)

◼ SUM

SELECT SUM(column) FROM tablename WHERE 조건;
: 숫자 컬럼의 합계를 계산

❔ 범죄 총 발생 건수는?

mysql> SELECT sum(case_number) from crime_status where status_type='발생';
+------------------+
| sum(case_number) |
+------------------+
|            92679 |
+------------------+
1 row in set (0.04 sec)

❔ 중부경찰서에서 검거된 총 범죄 건수는?

mysql> select sum(case_number) from crime_status 
	-> where police_station ='중부' and status_type='검거';
+------------------+
| sum(case_number) |
+------------------+
|             1406 |
+------------------+
1 row in set (0.05 sec)

◼ AVG

SELECT AVG(column) FROM tablename WHERE 조건;
: 숫자 컬럼의 평균을 계산

❔ 평균 폭력 검거 건수는?

mysql> SELECT AVG(case_number) from crime_status 
	-> where crime_type like '폭력' and status_type ='검거';
+------------------+
| AVG(case_number) |
+------------------+
|        1323.1935 |
+------------------+

◼ MIN

SELECT MIN(column) FROM tablename WHERE 조건;
: 숫자 컬럼 중 가장 작은 값을 찾아주는 함수

❔ 강도 발생 건수가 가장 적은 경우?

mysql> SELECT MIN(case_number) from crime_status 
	-> where crime_type like '강도' and status_type='발생';
+------------------+
| MIN(case_number) |
+------------------+
|                1 |
+------------------+

◼ MAX

SELECT MAX(column) FROM tablename WHERE 조건;
: 숫자 컬럼 중 가장 큰 값을 찾아주는 함수

❔ 살인이 가장 많이 검거된 건수?

mysql> SELECT MAX(case_number) from crime_status
	-> where crime_type like '살인' and status_type like '검거';
+------------------+
| MAX(case_number) |
+------------------+
|               12 |
+------------------+

---

◼ GROUP BY

SELECT COL1, COL2,... FROM tablename WHERE 조건 GROUP BY COL1, COL2,.. ORDER BY COL1, COL2,...
: 그룹화하여 데이터 조회

❔ crime_status 에서 경찰서별로 그룹화하여 경찰서 이름 조회

mysql> select police_station from crime_status 
	-> group by police_station order by police_station limit 5;
+----------------+
| police_station |
+----------------+
| 강남           |
| 강동           |
| 강북           |
| 강서           |
| 관악           |
+----------------+

❗distinct는 정렬 (order by) 사용할 수 없음.

❔ 경찰서 별 총 발생 범죄 건수 검색

mysql> select police_station, sum(case_number) 발생건수 from crime_status
    -> where status_type like '발생'     
    -> group by police_station order by 발생건수 desc limit 5;
+----------------+--------------+
| police_station | 발생건수     |
+----------------+--------------+
| 송파           |         5410 |
| 관악           |         5261 |
| 영등포         |         5217 |
| 강남           |         4754 |
| 강서           |         4415 |
+----------------+--------------+
5 rows in set (0.04 sec)

◼ HAVING

❗ 조건에 집계함수 포함되는 경우 WHERE 사용 불가. 그 경우 대신 HAVING 사용
SELECT COL1, COL2,... FROM tablename WHERE 조건 GROUP BY COL1, COL2,.. HAVING 조건 (aggregate Function) ORDER BY COL1, COL2,...

❔ 경찰서 별로 발생한 범죄 건수의 합이 4000건보다 큰 경우 검색

mysql> select police_station, sum(case_number) count from crime_status
    -> where status_type like '발생' group by police_station 
    -> having count > 4000;
+----------------+-------+
| police_station | count |
+----------------+-------+
| 영등포         |  5217 |
| 강남           |  4754 |
| 관악           |  5261 |
| 강서           |  4415 |
| 구로           |  4175 |
| 송파           |  5410 |
+----------------+-------+
6 rows in set (0.04 sec)

❔ 경찰서 별로 발생한 폭력과 절도의 범죄 건수 평균이 2000이상인 경우 검색

mysql>  select police_station, AVG(case_number) from crime_status
    -> where (crime_type like '폭력' or crime_type like '절도') and status_type like '발생'
    -> group by police_station having avg(case_number) >= 2000;
+----------------+------------------+
| police_station | AVG(case_number) |
+----------------+------------------+
| 영등포         |        2444.5000 |
| 강남           |        2112.0000 |
| 관악           |        2421.5000 |
| 강서           |        2067.0000 |
| 송파           |        2552.0000 |
+----------------+------------------+
5 rows in set (0.04 sec)