#50. GROUP BY : 카테고리 별 도서 판매량 집계하기 | 프로그래머스 | SQL 고득점 Kit

문제

 

입력 코드

1. JOIN

SELECT 
    book.CATEGORY, 
    SUM(sales.SALES) AS TOTAL_SALES
FROM BOOK AS book
    JOIN BOOK_SALES AS sales
    ON book.BOOK_ID = sales.BOOK_ID
WHERE sales.SALES_DATE LIKE "2022-01%"
GROUP BY book.CATEGORY
ORDER BY book.CATEGORY ASC;

 

2. WHERE

SELECT 
    CATEGORY, 
    SUM(SALES) AS TOTAL_SALES
FROM BOOK AS B,BOOK_SALES AS S
WHERE (B.BOOK_ID = S.BOOK_ID) 
    AND (SALES_DATE LIKE "2022-01%")
GROUP BY CATEGORY
ORDER BY CATEGORY;

 

3. LIKE 대신에 BETWEEN

SELECT
    B.CATEGORY,
    SUM(SALES) AS TOTAL_SALES
FROM BOOK AS B, BOOK_SALES AS BS
WHERE (B.BOOK_ID = BS.BOOK_ID)
    AND (BS.SALES_DATE BETWEEN '2022-01-01' AND '2022-01-31')
GROUP BY B.CATEGORY
ORDER BY B.CATEGORY ASC;

 

4. LIKE 대신에 DATE_FORMAT

SELECT 
    A.category, 
    SUM(B.sales)
FROM book A 
    JOIN book_sales B
    ON A.book_id = B.book_id
WHERE DATE_FORMAT(B.sales_date, '%Y-%m') = '2022-01'
GROUP BY A.category
ORDER BY A.category;

 

 

코드 설명

#SELECT #SUM #JOIN #WHERE #LIKE #GROUP BY #ORDER BY #BETWEEN #DATE_FORMAT

 

 

문제 출처

https://school.programmers.co.kr/learn/courses/30/parts/17044