[SQL 첫걸음] 20강 - 22강

5장 : 집계와 서브쿼리

20강 : 행 개수 구하기 - COUNT

1. COUNT로 행 개수 구하기

mysql> select * from sample51;
+------+------+----------+
| no   | name | quantity |
+------+------+----------+
|    1 | A    |        1 |
|    2 | A    |        2 |
|    3 | B    |       10 |
|    4 | C    |        3 |
|    5 | NULL |     NULL |
+------+------+----------+
5 rows in set (0.06 sec)

mysql> select count(*) from sample51;
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (0.01 sec)

# where 구 지정하기
mysql> select * from sample51 where name = 'A';
+------+------+----------+
| no   | name | quantity |
+------+------+----------+
|    1 | A    |        1 |
|    2 | A    |        2 |
+------+------+----------+
2 rows in set (0.00 sec)

mysql> select count(*) from sample51 where name = 'A';
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

 

2. 집계함수와 NULL값

- 집합 안에 NULL 값이 있을 경우 무시함

mysql> select * from sample51;
+------+------+----------+
| no   | name | quantity |
+------+------+----------+
|    1 | A    |        1 |
|    2 | A    |        2 |
|    3 | B    |       10 |
|    4 | C    |        3 |
|    5 | NULL |     NULL |
+------+------+----------+
5 rows in set (0.00 sec)

mysql> select COUNT(no), count(name) from sample51;
+-----------+-------------+
| COUNT(no) | count(name) |
+-----------+-------------+
|         5 |           4 |
+-----------+-------------+
1 row in set (0.00 sec)

 

3. DISTINCT로 중복 제거

mysql> select all name from sample51;
+------+
| name |
+------+
| A    |
| A    |
| B    |
| C    |
| NULL |
+------+
5 rows in set (0.00 sec)

mysql> select distinct name from sample51;
+------+
| name |
+------+
| A    |
| B    |
| C    |
| NULL |
+------+
4 rows in set (0.00 sec)

 

4. 집계함수에서 DISTINCT

mysql> select count(all name), count(distinct name) from sample51;
+-----------------+----------------------+
| count(all name) | count(distinct name) |
+-----------------+----------------------+
|               4 |                    3 |
+-----------------+----------------------+
1 row in set (0.00 sec)

 

21강 : COUNT 이외의 집계함수

1. SUM으로 합계 구하기

mysql> select * from sample51;
+------+------+----------+
| no   | name | quantity |
+------+------+----------+
|    1 | A    |        1 |
|    2 | A    |        2 |
|    3 | B    |       10 |
|    4 | C    |        3 |
|    5 | NULL |     NULL |
+------+------+----------+
5 rows in set (0.00 sec)

mysql> select sum(quantity) from sample51;
+---------------+
| sum(quantity) |
+---------------+
|            16 |
+---------------+
1 row in set (0.00 sec)

 

2. AVG로 평균내기

mysql> select * from sample51;
+------+------+----------+
| no   | name | quantity |
+------+------+----------+
|    1 | A    |        1 |
|    2 | A    |        2 |
|    3 | B    |       10 |
|    4 | C    |        3 |
|    5 | NULL |     NULL |
+------+------+----------+
5 rows in set (0.00 sec)

mysql> select avg(quantity), sum(quantity)/count(quantity) from sample51;
+---------------+-------------------------------+
| avg(quantity) | sum(quantity)/count(quantity) |
+---------------+-------------------------------+
|        4.0000 |                        4.0000 |
+---------------+-------------------------------+
1 row in set (0.00 sec)

# null을 0으로 간주하고 계산(원래는 무시)
mysql> select avg(case when quantity is null then 0 else quantity end) as avgnull0 from sample51;
+----------+
| avgnull0 |
+----------+
|   3.2000 |
+----------+
1 row in set (0.00 sec)

 

3. MIN · MAX로 최솟값 · 최댓값 구하기

mysql> select min(quantity), max(quantity), min(name), max(name) from sample51;
+---------------+---------------+-----------+-----------+
| min(quantity) | max(quantity) | min(name) | max(name) |
+---------------+---------------+-----------+-----------+
|             1 |            10 | A         | C         |
+---------------+---------------+-----------+-----------+
1 row in set (0.00 sec)

 

22강 : 그룹화 - GROUP BY

1. GROUP BY로 그룹화

- 결과적으로는 중복 제거 효과 있

mysql> select * from sample51;
+------+------+----------+
| no   | name | quantity |
+------+------+----------+
|    1 | A    |        1 |
|    2 | A    |        2 |
|    3 | B    |       10 |
|    4 | C    |        3 |
|    5 | NULL |     NULL |
+------+------+----------+
5 rows in set (0.00 sec)

mysql> select name from sample51 group by name;
+------+
| name |
+------+
| A    |
| B    |
| C    |
| NULL |
+------+
4 rows in set (0.00 sec)

# name 열을 그룹화해 계산하기
mysql> select name, count(name), sum(quantity) from sample51 group by name;
+------+-------------+---------------+
| name | count(name) | sum(quantity) |
+------+-------------+---------------+
| A    |           2 |             3 |
| B    |           1 |            10 |
| C    |           1 |             3 |
| NULL |           0 |          NULL |
+------+-------------+---------------+
4 rows in set (0.00 sec)

 

2. HAVING 구로 조건 지정

- where 구에서는 집계함수 사용 불가능

-> 내부 처리 순서 : where -> group by -> select -> order by

- 대신 having 구 사용

-> 내부 처리 순서 : where -> group by -> having -> select -> order by

# 오류 발생
mysql> select name, count(name) from sample51 where count(name) = 1 group by name;
ERROR 1111 (HY000): Invalid use of group function

mysql> select name, count(name) from sample51 group by name;
+------+-------------+
| name | count(name) |
+------+-------------+
| A    |           2 |
| B    |           1 |
| C    |           1 |
| NULL |           0 |
+------+-------------+
4 rows in set (0.00 sec)

# 정상 동작
mysql> select name, count(name) from sample51 group by name having count(name) = 1;
+------+-------------+
| name | count(name) |
+------+-------------+
| B    |           1 |
| C    |           1 |
+------+-------------+
2 rows in set (0.00 sec)

 

3. 복수열의 그룹화

mysql> select * from sample51;
+------+------+----------+
| no   | name | quantity |
+------+------+----------+
|    1 | A    |        1 |
|    2 | A    |        2 |
|    3 | B    |       10 |
|    4 | C    |        3 |
|    5 | NULL |     NULL |
+------+------+----------+
5 rows in set (0.00 sec)

# 오류 발생 : name 열 값이 A인 그룹의 quantity 열 값이 1, 2 두개라 무슨 값 반환해야할지 모름
mysql> select no, name, quantity from sample51 group by name;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'sample.sample51.no' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

# 집계함수로 집합을 하나의 값으로 계산
mysql> select min(no), name, sum(quantity) from sample51 group by name;
+---------+------+---------------+
| min(no) | name | sum(quantity) |
+---------+------+---------------+
|       1 | A    |             3 |
|       3 | B    |            10 |
|       4 | C    |             3 |
|       5 | NULL |          NULL |
+---------+------+---------------+
4 rows in set (0.00 sec)

# group by에서 지정한 열은 select 구에 그대로 지정 가능
mysql> select no, quantity from sample51 group by no, quantity;
+------+----------+
| no   | quantity |
+------+----------+
|    1 |        1 |
|    2 |        2 |
|    3 |       10 |
|    4 |        3 |
|    5 |     NULL |
+------+----------+
5 rows in set (0.00 sec)

 

4. 결괏값 정렬

- order by로 정렬 가능

mysql> select name, count(name), sum(quantity) from sample51 group by name order by sum(quantity) desc;
+------+-------------+---------------+
| name | count(name) | sum(quantity) |
+------+-------------+---------------+
| B    |           1 |            10 |
| A    |           2 |             3 |
| C    |           1 |             3 |
| NULL |           0 |          NULL |
+------+-------------+---------------+
4 rows in set (0.00 sec)