[PRO] 저자 별 카테고리 별 매출액 집계하기 - Lv4

💻 SQL 고득점 Kit > GROUP BY > 문제

👉 제출 코드

MySQL

SELECT A.AUTHOR_ID, A.AUTHOR_NAME, B_S.CATEGORY, SUM(B_S.TOTAL_SALES) AS SALES
FROM AUTHOR A JOIN (
    SELECT B.BOOK_ID, B.CATEGORY, B.AUTHOR_ID, B.PRICE * S.SALES AS TOTAL_SALES
    FROM BOOK B JOIN BOOK_SALES S
    ON B.BOOK_ID = S.BOOK_ID
    WHERE S.SALES_DATE LIKE '2022-01-%') B_S
ON A.AUTHOR_ID = B_S.AUTHOR_ID
GROUP BY A.AUTHOR_ID, B_S.CATEGORY
ORDER BY A.AUTHOR_ID ASC, B_S.CATEGORY DESC

Oracle

SELECT A.AUTHOR_ID, A.AUTHOR_NAME, B_S.CATEGORY, SUM(B_S.TOTAL_SALES) AS SALES
FROM AUTHOR A JOIN (
    SELECT B.BOOK_ID, B.CATEGORY, B.AUTHOR_ID, B.PRICE * S.SALES TOTAL_SALES
    FROM BOOK B JOIN BOOK_SALES S
    ON B.BOOK_ID = S.BOOK_ID
    WHERE TO_CHAR(S.SALES_DATE, 'YYYY-MM') = '2022-01') B_S
ON A.AUTHOR_ID = B_S.AUTHOR_ID
GROUP BY A.AUTHOR_ID, A.AUTHOR_NAME, B_S.CATEGORY
ORDER BY A.AUTHOR_ID ASC, B_S.CATEGORY DESC

• 특정 날짜를 조회하는 코드 부분만 다릅니다.
• MySQL
  WHERE S.SALES_DATE LIKE '2022-01-%'
• Oracle
  TO_CHAR(S.SALES_DATE, 'YYYY-MM') = '2022-01'