Aggregate Functions(집계 함수)

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

1. COUNT

• 총 갯수를 계산해주는 함수

// police_station 테이블의 데이터 총 개수
mysql> select count(*) from police_station;
+----------+
| count(*) |
+----------+
|       31 |
+----------+
1 row in set (0.02 sec)

// crime_status 테이블에서 police_station 컬럼의 고유 값 개수 (경찰서는 총 몇 군데?)
mysql> select count(distinct police_station) from crime_status;
+--------------------------------+
| count(distinct police_station) |
+--------------------------------+
|                             31 |
+--------------------------------+
1 row in set (0.07 sec)

// crime_type은 총 몇 가지? 
mysql> select count(distinct crime_stype) from crime_status;
+-----------------------------+
| count(distinct crime_stype) |
+-----------------------------+
|                           6 |
+-----------------------------+
1 row in set (0.01 sec)

// 6개 있는 게 맞음.
mysql> select distinct crime_stype from crime_status; 
+---------------+
| crime_stype    |
+---------------+
| 살인          |
| 강도          |
| 강간,추행     |
| 절도          |
| 폭력          |
| 강간          |
+---------------+
6 rows in set (0.02 sec)

// (컬럼명에 오타가 있길래 수정함..)
mysql> alter table crime_status change crime_stype crime_type varchar(16);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

2. SUM

// 범죄 총 발생 건수
mysql> select sum(case_number) from crime_status where status_type='발생';
+------------------+
| sum(case_number) |
+------------------+
|            92679 |
+------------------+
1 row in set (0.01 sec)

// 살인의 총 발생 건수
mysql> select sum(case_number) from crime_status where crime_type='살인' and status_type='발생';
+------------------+
| sum(case_number) |
+------------------+
|              141 |
+------------------+
1 row in set (0.04 sec)

// 중부경찰서에서 검거된 총 범죄 건수는?
mysql> select sum(case_number) from crime_status where reference='서울중부경찰서' and status_type='검거' 
+------------------+
| sum(case_number) |
+------------------+
|             1406 |
+------------------+
1 row in set (0.01 sec)

// 1. police_station에서 경찰서는 총 몇 개이고, 각각 경찰서 이름은 무엇인지 확인하세요. 
mysql> select count(distinct name) from police_station;
+----------------------+
| count(distinct name) |
+----------------------+
|                   31 |
+----------------------+
1 row in set (0.02 sec)
mysql> select distinct name from police_station;
+--------------------------+
| name                     |
+--------------------------+
| 서울강남경찰서           |
| 서울강동경찰서           |
| 서울강북경찰서           |
| 서울강서경찰서           |
| 서울관악경찰서           |
| 서울광진경찰서           |
| 서울구로경찰서           |
| 서울금천경찰서           |
| 서울남대문경찰서         |
| 서울노원경찰서           |
| 서울도봉경찰서           |
| 서울동대문경찰서         |
| 서울동작경찰서           |
| 서울마포경찰서           |
| 서울방배경찰서           |
| 서울서대문경찰서         |
| 서울서부경찰서           |
| 서울서초경찰서           |
| 서울성동경찰서           |
| 서울성북경찰서           |
| 서울송파경찰서           |
| 서울수서경찰서           |
| 서울양천경찰서           |
| 서울영등포경찰서         |
| 서울용산경찰서           |
| 서울은평경찰서           |
| 서울종로경찰서           |
| 서울종암경찰서           |
| 서울중랑경찰서           |
| 서울중부경찰서           |
| 서울혜화경찰서           |
+--------------------------+
31 rows in set (0.01 sec)

// 2. crime_status에서 status_type은 총 몇 개이고, 각각 타입은 무엇인지 확인하세요. 
mysql> select count(distinct status_type) from crime_status;
+-----------------------------+
| count(distinct status_type) |
+-----------------------------+
|                           2 |
+-----------------------------+
1 row in set (0.02 sec)

mysql> select distinct status_type from crime_status;
+-------------+
| status_type |
+-------------+
| 발생        |
| 검거        |
+-------------+
2 rows in set (0.02 sec)

// 3. 종로경찰서와 남대문 경찰서의 강도 발생 건수의 합
mysql> select sum(case_number)
    -> from crime_status
    -> where crime_type='강도' and status_type='발생' and police_station in ('종로', '남대문');
+------------------+
| sum(case_number) |
+------------------+
|                6 |
+------------------+
1 row in set (0.01 sec)


// 4. 폭력 범죄의 검거 건수의 합
mysql> select sum(case_number)    
    -> from crime_status
    -> where crime_type='폭력'
    -> and status_type='검거';
+------------------+
| sum(case_number) |
+------------------+
|            41019 |
+------------------+
1 row in set (0.02 sec)

3. Avg

• 숫자 컬럼의 평균을 계산

// 평균 폭력 검거 건수
mysql> select AVG(case_number)
    -> from crime_status
    -> where crime_type like '폭력' and status_type='검거';
+------------------+
| AVG(case_number) |
+------------------+
|        1323.1935 |
+------------------+
1 row in set (0.01 sec)

// 중부경찰서 범죄 평균 발생 건수

mysql> select AVG(case_number) from crime_status
    -> where police_station like '중부' and status_type='발생';
+------------------+
| AVG(case_number) |
+------------------+
|         411.4000 |
+------------------+
1 row in set (0.02 sec)

4. MIN/MAX

• MIN: 숫자 컬럼 중 가장 작은 값을 찾아주는 함수
• MAX: 숫자 컬럼 중 가장 큰 값을 찾아주는 함수
  select min(case_number) from crime_status

//1. 강도 발생 건수가 가장 적은 경우는 몇 건?
mysql> select min(case_number) from crime_status
    -> where status_type='발생' and crime_type='강도';
+------------------+
| min(case_number) |
+------------------+
|                1 |
+------------------+
1 row in set (0.01 sec)

// 2. 중부경찰서에서 가장 낮은 검거 건수는?
mysql> select min(case_number) from crime_status
    -> where status_type='검거' and police_station like '중부';
+------------------+
| min(case_number) |
+------------------+
|                1 |
+------------------+
1 row in set (0.02 sec)

// 3. 살인이 가장 많이 검거된 건수는?
mysql> select max(case_number) from crime_status
    -> where crime_type='살인' and status_type='검거';
+------------------+
| max(case_number) |
+------------------+
|               12 |
+------------------+
1 row in set (0.03 sec)


// 4. 강남 경찰서에서 가장 많이 발생한 범죄 건수는?
mysql> select max(case_number) from crime_status
    -> where police_station like '강남' and status_type='발생';
+------------------+
| max(case_number) |
+------------------+
|             2283 |
+------------------+
1 row in set (0.02 sec)

// 5. 성북경찰서에서 가장 낮은 범죄 발생 건수는? -> 살인이 0건
mysql> select min(case_number) from crime_status
    -> where police_station like '성북' and status_type='발생';
+------------------+
| min(case_number) |
+------------------+
|                0 |
+------------------+
1 row in set (0.10 sec)

mysql> select * from crime_status
    -> where police_station like '성북' and status_type='발생';
+------+----------------+------------+-------------+-------------+-----------------------+
| year | police_station | crime_type | status_type | case_number | reference             |
+------+----------------+------------+-------------+-------------+-----------------------+
| 2020 | 성북           | 살인       | 발생        |           0 | 서울성북경찰서        |
| 2020 | 성북           | 강도       | 발생        |           2 | 서울성북경찰서        |
| 2020 | 성북           | 강간       | 발생        |          95 | 서울성북경찰서        |
| 2020 | 성북           | 절도       | 발생        |         439 | 서울성북경찰서        |
| 2020 | 성북           | 폭력       | 발생        |         672 | 서울성북경찰서        |
+------+----------------+------------+-------------+-------------+-----------------------+
5 rows in set (0.01 sec)

5. GROUP BY

• 그룹화하여 데이터를 조회
• 단, DISTINCT 사용하는 경우 ORDER BY 사용할 수 없음.

SELECT col1, col2, ...
FROM tablename
WHERE condition
GROUP BY col1, col2, ...
ORDER BY col1, col2, ...

// 경찰서 종류별 건수 검색
```sql
mysql> SELECT police_station, sum(case_number) 
       FROM crime_status 
       GROUP BY police_station 
       ORDER BY sum(case_number);
+----------------+------------------+
| police_station | sum(case_number) |
+----------------+------------------+
| 방배           |             1412 |
| 성북           |             2130 |
| 남대문         |             2271 |
| 종암           |             2384 |
| 혜화           |             2402 |
| 서부           |             2427 |
| 종로           |             2873 |
| 중부           |             3463 |
| 은평           |             3494 |
| 도봉           |             3669 |
| 성동           |             4136 |
| 서대문         |             4239 |
| 수서           |             4474 |
| 금천           |             4564 |
| 용산           |             5025 |
| 강북           |             5029 |
| 동작           |             5286 |
| 양천           |             5302 |
| 동대문         |             5866 |
| 광진           |             6112 |
| 마포           |             6222 |
| 서초           |             6241 |
| 노원           |             6324 |
| 강동           |             6450 |
| 중랑           |             6507 |
| 구로           |             7159 |
| 강서           |             7808 |
| 강남           |             8127 |
| 영등포         |             8588 |
| 송파           |             8954 |
| 관악           |             9119 |
+----------------+------------------+
31 rows in set (0.01 sec)


// 경찰서 종류 검색: 
SELECT DISTINCT police_station
FROM crime_status


// 경찰서별 총 발생 범죄 건수 검색
mysql> select police_station, sum(case_number) 발생건수
    -> from crime_status
    -> where status_type = '발생' 
    -> group by police_station
    -> order by 발생건수 DESC;
+----------------+--------------+
| police_station | 발생건수     |
+----------------+--------------+
| 송파           |         5410 |
| 관악           |         5261 |
| 영등포         |         5217 |
| 강남           |         4754 |
| 강서           |         4415 |
| 구로           |         4175 |
| 강동           |         3788 |
| 서초           |         3765 |
| 노원           |         3743 |
| 중랑           |         3726 |
| 마포           |         3688 |
| 광진           |         3601 |
| 동대문         |         3401 |
| 양천           |         3216 |
| 동작           |         3200 |
| 용산           |         2969 |
| 강북           |         2770 |
| 수서           |         2602 |
| 금천           |         2598 |
| 서대문         |         2533 |
| 성동           |         2362 |
| 도봉           |         2179 |
| 중부           |         2057 |
| 은평           |         2028 |
| 종로           |         1694 |
| 혜화           |         1408 |
| 서부           |         1362 |
| 종암           |         1359 |
| 남대문         |         1354 |
| 성북           |         1208 |
| 방배           |          836 |
+----------------+--------------+
31 rows in set (0.02 sec)

// 경찰서별 평균 범죄 발생건수와 평균 범죄 검거건수를 검색
mysql> select police_station 경찰서, status_type, avg(case_number)
    -> from crime_status
    -> group by police_station, status_type
    -> limit 8;
+-----------+-------------+------------------+
| 경찰서    | status_type | avg(case_number) |
+-----------+-------------+------------------+
| 중부      | 발생        |         411.4000 |
| 중부      | 검거        |         281.2000 |
| 종로      | 발생        |         338.8000 |
| 종로      | 검거        |         235.8000 |
| 남대문    | 발생        |         270.8000 |
| 남대문    | 검거        |         183.4000 |
| 서대문    | 발생        |         506.6000 |
| 서대문    | 검거        |         341.2000 |
+-----------+-------------+------------------+
8 rows in set (0.01 sec)

6. HAVING

• 항상 Group by 뒤에 위치하며, Group by 된 이후 특정 필드로 그룹화된 새로운 테이블에 조건을 주는 것.
• WHERE과 차이점
  • 전체 테이블 자체에서 조건을 걸고 싶으면 WHERE을,
  • 전체 테이블을 그룹화한 후 조건을 걸고 싶으면 HAVING을 사용함.
  • 집계함수(COUNT, MIN, MAX, SUM, AVG 등)는 HAVING과만 사용할 수 있다.
• Having까지 적용해서 만든 테이블을 정렬하는 것이기 때문에 Group by -> Having -> Order by 순서로 작성해야 함 주의!

// 경찰서별로 발생한 범죄 검수의 합이 4000건 보다 큰 경우를 검색
select police_station 경찰서, sum(case_number) 범죄발생건수
from crime_status
where status_type like '발생'
group by police_station
having 범죄발생건수 > 4000;

+-----------+--------------------+
| 경찰서    | 범죄발생건수       |
+-----------+--------------------+
| 영등포    |               5217 |
| 강남      |               4754 |
| 관악      |               5261 |
| 강서      |               4415 |
| 구로      |               4175 |
| 송파      |               5410 |
+-----------+--------------------+
6 rows in set (0.13 sec)

// 경찰서별로 발생한 폭력과 절도의 범죄 건수 평균이 2000 이상인 경우를 검색
mysql> select police_station 경찰서, avg(case_number)
    -> from crime_status
    -> where status_type like '발생' 
    -> and crime_type in ('폭력', '절도')
    -> group by police_station
    -> having avg(case_number) >= 2000;
+-----------+------------------+
| 경찰서    | avg(case_number) |
+-----------+------------------+
| 영등포    |        2444.5000 |
| 강남      |        2112.0000 |
| 관악      |        2421.5000 |
| 강서      |        2067.0000 |
| 송파      |        2552.0000 |
+-----------+------------------+
5 rows in set (0.03 sec)

//경찰서별로 절도 범죄 평균 발생 건수를 가장 많은 건수 순으로 10개 검색하고 확인하세요.
mysql> select police_station 경찰서, avg(case_number) 절도범죄평균발생건수
    -> from crime_status
    -> where status_type like '발생' 
    -> and crime_type like '절도'
    -> group by police_station
    -> order by 절도범죄평균발생건수 DESC
    -> limit 10;
+-----------+--------------------------------+
| 경찰서    | 절도범죄평균발생건수           |
+-----------+--------------------------------+
| 송파      |                      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 경찰서, max(case_number)
    -> from crime_status
    -> where status_type like '검거'
    -> group by police_station
    -> order by max(case_number)
    -> limit 5;
+-----------+------------------+
| 경찰서    | max(case_number) |
+-----------+------------------+
| 방배      |              365 |
| 남대문    |              494 |
| 성북      |              610 |
| 혜화      |              628 |
| 종암      |              630 |
+-----------+------------------+
5 rows in set (0.02 sec)

// 경찰서별로 가장 적게 검거한 건수 중 4건보다 큰 경우를 건수가 큰 순으로 정렬하여 검색하세요. 
mysql> select police_station 경찰서, min(case_number)
    -> from crime_status
    -> where status_type like '검거'
    -> group by police_station
    -> having min(case_number) > 4
    -> order by min(case_number) desc;
+-----------+------------------+
| 경찰서    | min(case_number) |
+-----------+------------------+
| 중랑      |                6 |
| 송파      |                6 |
| 동대문    |                5 |
| 강서      |                5 |
| 구로      |                5 |
+-----------+------------------+
5 rows in set (0.01 sec)

// '대문'으로 끝나는 이름의 경찰서별 범죄발생 건수의 평균이 500건 이상인 경우를 검색하세요. 
mysql> select police_station 경찰서, avg(case_number) 평균범죄발생건수
    -> from crime_status
    -> where status_type like '발생' and police_station like '%대문'
    -> group by police_station
    -> having 평균범죄발생건수 >= 500;
+-----------+--------------------------+
| 경찰서    | 평균범죄발생건수         |
+-----------+--------------------------+
| 서대문    |                 506.6000 |
| 동대문    |                 680.2000 |
+-----------+--------------------------+
2 rows in set (0.01 sec)

7. Case, When

• 기존의 열과 조건문을 이용해서 새로운 열을 만들고 싶을 때

SELECT CASE
       	WHEN 조건 1 THEN '이름1'
        WHEN 조건 1 THEN '이름1'
        WHEN 조건 1 THEN '이름1'
        ELSE '이름4' END AS 새로운 열 이름

• 조건문은 순차적으로 진행한다. 첫번째 조건을 만족하면 다음 조건은 확인하지 않고 바로 첫번째 조건의 이름을 부여한다.
• 마지막 ELSE는 생략 가능하다. 만약 ELSE 설정을 안했다면, ELSE에 해당하는 값에 null이 생김.
• END는 꼭 붙여줘야 한다.

SELECT OrderID, Quantity,
  CASE 
      WHEN Quantity > 30 THEN 'The quantity is greater than 30'
      WHEN Quantity = 30 THEN 'The quantity is 30'
      ELSE 'The quantity is under 30'
      END AS QuantityText
  FROM OrderDetails;