MySQL group by, count, sum, avg, max, min, sub query 사용법

테스트용 테이블

-- 테스트용 테이블을 만드는 코드
INSERT INTO books (title, author_fname, author_lname, released_year, stock_quantity, pages)
VALUES
('The Namesake', 'Jhumpa', 'Lahiri', 2003, 32, 291),
('Norse Mythology', 'Neil', 'Gaiman',2016, 43, 304),
('American Gods', 'Neil', 'Gaiman', 2001, 12, 465),
('Interpreter of Maladies', 'Jhumpa', 'Lahiri', 1996, 97, 198),
('A Hologram for the King: A Novel', 'Dave', 'Eggers', 2012, 154, 352),
('The Circle', 'Dave', 'Eggers', 2013, 26, 504),
('The Amazing Adventures of Kavalier & Clay', 'Michael', 'Chabon', 2000, 68, 634),
('Just Kids', 'Patti', 'Smith', 2010, 55, 304),
('A Heartbreaking Work of Staggering Genius', 'Dave', 'Eggers', 2001, 104, 437),
('Coraline', 'Neil', 'Gaiman', 2003, 100, 208),
('What We Talk About When We Talk About Love: Stories', 'Raymond', 'Carver', 1981, 23, 176),
("Where I'm Calling From: Selected Stories", 'Raymond', 'Carver', 1989, 12, 526),
('White Noise', 'Don', 'DeLillo', 1985, 49, 320),
('Cannery Row', 'John', 'Steinbeck', 1945, 95, 181),
('Oblivion: Stories', 'David', 'Foster Wallace', 2004, 172, 329),
('Consider the Lobster', 'David', 'Foster Wallace', 2005, 92, 343);

-- 갯수를 세는 함수 count()
-- books 테이블의 데이터 갯수는 ?????
select count(*)
from books;

-- author_lname 의 유니크한 데이터의 갯수는??? 판다스의 nunique와 같다.
select count(distinct author_lname)
from books;

-- 책 제목에 the 가 들어간 책은 몇권입니까??
select count(*)
from books
where title like "%the%";

-- 집계하는 함수. ~~ 별로. group by 키워드
-- author_lname 별로 몇권의 책을 썼는지, 작가 이름과 책권수를 가져오시오.
select author_lname, count(*) as count
from books
group by author_lname;

-- 년도별로 각각 몇권의 책이 출간되었는지, 년도와 출간수를 가져와서, 내림차순으로 정렬하시오.
select released_year, count(title) as count
from books
group by released_year
order by released_year desc;

-- 최소값 구하는 함수 min()
-- 출간년도가 가장 빠른책은 몇년도인가?
select min(released_year) as min_year
from books;

-- 최대값 구하는 함수 max()
-- 페이지수가 가장 많은 책은 몇페이지로 되어있나? => 페이지의 최대값을 찾아라!
select max(pages) as max_pages
from books;

-- 페이지수의 최대값과 최소값을 가져오시오.
select max(pages) as max_pages, min(pages) as min_pages
from books;

-- 페이지수가 가장 긴 책의 제목은 ???

-- 잘못된 SQL
select title, max(pages)
from books;

-- 방법1.
select title, pages
from books
order by pages desc
limit 0,1;

-- 방법2. max 값 구해서, sub query 하는 방법 
select *
from books
where pages = 634;

-- 634 를 쓰는건 번거롭기 때문에 아래와 같이 한다.
select *
from books
where pages = (select max(pages) from books);

-- 재고가 가장 적은 책의 책이름과 작가 fname, lname을 가져오시오.
select title, author_fname, author_lname
from books
where stock_quantity = (select min(stock_quantity) from books);

-- author_lname 과 author_fname 별로 (작가의 풀네임별로) 
-- 그 작가의 이름과(풀 네임)과 출간년도의 최소값을 보여주세요
-- (각 작가별로, 최초 책 발간한 년도가 언제입니까?)
select concat(author_lname," ", author_fname), min(released_year)
from books
group by author_lname, author_fname;

-- 각 작가별(풀네임)로 자신의 쓴 책중 가장 긴 책의 페이지수를 보여주세요
-- (작가의 이름과 페이지수)
select max(pages), concat(author_fname, ' ', author_lname)
from books
group by author_lname, author_fname;

-- 값을 모두 더해주는 sum() 함수
-- books 테이블의 모든 책 페이지수를 다 더하면 ??
select sum(pages)
from books;

-- 각 작가별로, 자신이 쓴 책의 페이지수를 모두 더해서 보여주시오.
-- 작가이름과 페이지수합 을 보여주세요.
select concat(author_fname,' ', author_lname),sum(pages)
from books
group by author_fname, author_lname;

-- 평균구하는 함수 avg()
-- books 테이블의 페이지수 평균을 구하시오.
select avg(pages)
from books;

-- 년도별로 재고의 평균을 구하시오. 년도와 재고평균만 나오도록 하시오.
select released_year, avg(stock_quantity)
from books
group by released_year;