UDEMY - The Ultimate MySQL Bootca…The Ultimate MySQL Bootcamp: Go from SQL Beginner to Expert를 수강하며 정리한 글
GROUP BY
"GROUP BY" summarized or aggregates identical data into single rows
GROUP BY 강의의 경우 예전 MySQL버전(5.5버전)으로 촬영되있어 현재 버전(8.0.23. 버전확인 명령어 : mysql> SELECT version();)과 맞지 않는다.
Colt's version of MySQL Server (5.5) did not have this SQL mode enabled by default, so his SELECT query produced "goofy" (my term, not MySQL's) results because it gave a title in the query: SELECT title, author_lname FROM books GROUP BY author_lname;.
MySQL versions 5.7 and later have the 'only_full_group_by' SQL mode enabled by default and that's why you get an error. The error message suggests that you include title in the GROUP BY clause, but if you did that, then since each title is unique (different) within each author, you'd get the same result as if you just did a normal query with no GROUP BY clause.
이 오류를 고치는 방법으로 Sql mode를 바꿀 수도 있지만 (only_full_group_by가 아니게) 잘못하면 데이터를 망칠 수 있기 때문에 나는 앞부분
mysql> SELECT title, author_lname FROM books GROUP BY author_lname;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'book_shop.books.title' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
은 빼고 하겠다. 이 SQL문을 쓴 의도는 author_lname으로 작가 last name별로 묶어 그 작가가 쓴 작품title을 보여주는 것일 거다. 하지만 한 작가가 여러 작품을 가지고 있을 수 있고, 그럼 author_lname당 작품 여러개를 보여줘야 하는데 그렇게 되지 않기 때문에 발생한 오류이다. MySQL 5.5이하 버전에서는 타이틀을 하나만 보여주는 것으로 실행이 됬었던 것 같은데 5.7버전부터는 only_full_group_by가 기본 모드로 적용되어 있어 에러가 발생한다. 모드를 고쳐줄 수도 있지만(135강 질의응답 부분 참조) 데이터 손상이나 다른 에러가 발생할 수 있기 때문에 나는 고치지 않고 위와 같은 SQL문은 쓰지 않겠다.
그럼 이제 위 SQL문의 의도와 비슷하게, author_lname당 쓴 작품의 수를 구해보자.
mysql> SELECT author_lname, COUNT(*) FROM books GROUP BY author_lname;
+----------------+----------+
| author_lname | COUNT(*) |
+----------------+----------+
| Lahiri | 2 |
| Gaiman | 2 |
| Eggers | 3 |
| Chabon | 1 |
| Smith | 1 |
| Carver | 2 |
| DeLillo | 1 |
| Steinbeck | 1 |
| Foster Wallace | 2 |
| Harris | 2 |
| Saunders | 1 |
+----------------+----------+
11 rows in set (0.00 sec)이 SQL문은 정상적으로 실행이 되는데, author_lname당 title이라는 칼럼의 데이터 들을 굳이 하나하나 보여주지 않고 count해서 그 수를 보여주면 되기 때문에 정상적으로 실행이 되는 것이다. 이렇게 GROUP BY는 aggregate function이랑 주로 쓴다.
ex) AVG(평균)은 아직 나오지 않았지만 예로 보여주자면
mysql> SELECT AVG(pages), author_lname FROM books GROUP BY author_lname;
+------------+----------------+
| AVG(pages) | author_lname |
+------------+----------------+
| 244.5000 | Lahiri |
| 256.0000 | Gaiman |
| 431.0000 | Eggers |
| 634.0000 | Chabon |
| 304.0000 | Smith |
| 351.0000 | Carver |
| 320.0000 | DeLillo |
| 181.0000 | Steinbeck |
| 336.0000 | Foster Wallace |
| 342.0000 | Harris |
| 367.0000 | Saunders |
+------------+----------------+
11 rows in set (0.00 sec)이렇게 author_lname마다 pages의 평균을 보여줄 수 있다.
풀 네임으로 작가마다 쓴 글을 카운트 하고싶을 때는 어떻게 해야할까?
mysql> SELECT COUNT(*), CONCAT(author_fname,' ',author_lname)
FROM books
GROUP BY CONCAT(author_fname,' ',author_lname);
+----------+---------------------------------------+
| COUNT(*) | CONCAT(author_fname,' ',author_lname) |
+----------+---------------------------------------+
| 2 | Jhumpa Lahiri |
| 2 | Neil Gaiman |
| 3 | Dave Eggers |
| 1 | Michael Chabon |
| 1 | Patti Smith |
| 2 | Raymond Carver |
| 1 | Don DeLillo |
| 1 | John Steinbeck |
| 2 | David Foster Wallace |
| 1 | Dan Harris |
| 1 | Freida Harris |
| 1 | George Saunders |
+----------+---------------------------------------+
12 rows in set (0.00 sec)이렇게 하면 Dan Harris와 Freida Harris(author_fname이 다르고 author_lname이 같은)가 구별되서 카운트 되는 것을 볼 수 있다.
또는 이렇게 해줘도 된다.
mysql> SELECT author_fname, author_lname, COUNT(*) FROM books GROUP BY author_fname, author_lname;
+--------------+----------------+----------+
| author_fname | author_lname | count(*) |
+--------------+----------------+----------+
| Jhumpa | Lahiri | 2 |
| Neil | Gaiman | 2 |
| Dave | Eggers | 3 |
| Michael | Chabon | 1 |
| Patti | Smith | 1 |
| Raymond | Carver | 2 |
| Don | DeLillo | 1 |
| John | Steinbeck | 1 |
| David | Foster Wallace | 2 |
| Dan | Harris | 1 |
| Freida | Harris | 1 |
| George | Saunders | 1 |
+--------------+----------------+----------+
12 rows in set (0.00 sec)mysql> SELECT CONCAT('In ', released_year,', ', count(*), 'books released')
FROM books GROUP BY released_year;
+---------------------------------------------------------------+
| CONCAT('In ', released_year,', ', count(*), 'books released') |
+---------------------------------------------------------------+
| In 2003, 2books released |
| In 2016, 1books released |
| In 1996, 1books released |
| In 2012, 1books released |
| In 2013, 1books released |
| In 2000, 1books released |
| In 2010, 1books released |
| In 2001, 2books released |
| In 1981, 1books released |
| In 1989, 1books released |
| In 1985, 1books released |
| In 1945, 1books released |
| In 2004, 1books released |
| In 2005, 1books released |
| In 2014, 1books released |
| In 2017, 1books released |
+---------------------------------------------------------------+
16 rows in set (0.00 sec)