MySQL_section9_The Magic of Aggregate Functions

#1. COUNT
::SELECT COUNT(컬럼명) FROM <테이블명>;
EX1. SELECT COUNT(*) FROM books;
EX2. SELECT COUNT(author_fname) FROM books;
EX3. SELECT COUNT(DISTINCT author_fname) FROM books;
EX4. SELECT COUNT(DISTINCT author_lname) FRMO books;
EX5. SELECT COUNT(DISTINCT author_fname, author_lname) FROM books;
EX6. How many titles contain 'the'?
        SELECT COUNT(title) FROM books WHERE title LIKE '%the%';
        SELECT COUNT(*) FROM books WEHRE title LIKE '%the%';

 

#2. GROUP BY

!!Mysql version이 5.7이상인 경우 다음 코드를 넣어 준 뒤 실행
set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

::SELECT <컬럼명> FROM <테이블명> GROUP BY <기준 컬럼명>;
EX1. SELECT author_lname, COUNT(*) FROM books GROUP BY author_lname;
EX2. SELECT COUNT(*) FROM books GROUP BY author_lname;
EX3. SELECT COUNT(*) FROM books GROUP BY author_lname, author_fname;
EX4. SELECT released_year, COUNT(*) FROM books GROUP BY released_year;
EX5. SELECT COUNT(released_year) FROM books GROUP BY released_year;    #EX4와 출력값 같음
EX6. SELECT title, released_year, COUNT(*) FROM books GROUP BY released_year;    #이경우 title은 대표적인것 하나만 보여줌
EX7. SELECT 
        CONCAT('In ', released_year, COUNT(*), ' book(s) released') AS year
        FROM books GROUP BY released_year;

 

#3. MIN AND MAX
::SELECT MIN(컬럼명) FROM <테이블명>;
::SELECT MAX(컬럼명) FROM <테이블명>;
EX1. SELECT MIN(released_year) FROM books;
EX2. SELECT MIN(pages) FROM books;
EX3. SELECT MAX(released_year) FROM books;
EX4. SELECT MAX(pages) FROM books;
EX5. SELECT MAX(pages), title FROM books;
EX6. SELECT MIN(released_year), title, stock_quantity FROM books;

 

#4. MIN AND MAX with WEHRE, ORDER BY
EX1. SELECT * FROM books WHERE pages = (SELECT MIN(pages) FROM books);
EX2. SELECT title, pages FROM books WHERE pages = (SELECT MAX(pages) FROM books);
EX3. SELECT title, pages FROM books WHERE pages = (SELECT MIN(pages) FROM books);
EX4. SELECT * FROM books ORDER BY pages ASC LIMIT 1;
EX5. SELECT title, pages FROM books ORDER BY pages ASC LiMIT 1;
EX6. SELECT * FROM books ORDER BY pages DESC LIMIT 1;

 

#5. Using MIN and MAX with GROUP BY

(1) Find the year each author published their first book.
        SELECT author_fname, author_lname, MIN(released_year) FROM books GROUP BY author_lname, author_fname;
(2) Find the longest page count for each author
        SELECT CONCAT(author_fname, ' ', author_lname) AS author, MAX(pages) AS 'longest book' FROM books GROUP BY author_lname, author_fname;

 

 

#6. SUM
::SELECT SUM(컬럼명) FROM <테이블명>;
EX1. SELECT SUM(pages) FROM books;
EX2. SELECT SUM(released_year) FROM books;
EX3. SELECT author_fname, author_lname, SUM(pages) FROM books GROUP BY author_lname, author_fname;
EX4. SELECT author_fname, author_lname, SUM(released_year) FROM books GROUP BY author_lname, author_fname;

 

#7. AVG
::SELECT AVG(컬럼명) FROM <테이블명>;
EX1. SELECT AVG(released_year) FROM books;
EX2. SELECT AVG(pages) FROM books;
EX3. SEELCT released_year, AVG(stock_quantity) FROM books GROUP BY released_year;
EX4. SELECT author_fname, author_lname, AVG(pages) FROM books GROUP BY author_fname, author_lname;

 

#8. EXERCISE

(1) Print the number of books in the database
        SELECT COUNT(*) FROM books;

(2) Print out how many books were released in each year
        SELECT COUNT(*) FROM books GROUP BY released_year;

(3) Print out the total number of books in stock
        SELECT SUM(stock_quantity) FROM books;

(4) Find the average released_year for each author
        SELECT author_fname, author_lname, AVG(released_year) FROM books GROUP BY author_fname, author_lname;

(5) Find the full name of the author who wrote the longest book

SELECT author_fname, author_lname, pages FROM books WHERE pages = (SELECT MAX(pages) FROM books);
 

SELECT CONCAT(author_lname, ' ', author_fname) AS author, pages FROM books WEHRE pagew = (SELECT MAX(pages) FROM books);

 

SELECT CONCAT(author_lname, ' ', author_fname) AS author FROM books ORDER BY pages DESC LIMIT 1;

(6) SELECT released_year AS year, COUNT(title) AS '# books', AVG(pages) AS 'avg pages' FROM books GROUP BY released_year;