[GROUP BY/LEVEL4] 저자 별 카테고리 별 매출액 집계하기 (MySQL/Oracle)

MySQL

SELECT A.AUTHOR_ID
, B.AUTHOR_NAME
, A.CATEGORY
, SUM(A.PRICE * C.SALES) AS TOTAL_SALES
FROM BOOK A, AUTHOR B, BOOK_SALES C
WHERE A.AUTHOR_ID = B.AUTHOR_ID
AND A.BOOK_ID = C.BOOK_ID
AND YEAR(C.SALES_DATE) = 2022 AND MONTH(C.SALES_DATE) = 1
GROUP BY A.AUTHOR_ID
, B.AUTHOR_NAME
, A.CATEGORY
ORDER BY A.AUTHOR_ID, A.CATEGORY DESC

 

---

Oracle

SELECT A.AUTHOR_ID
, B.AUTHOR_NAME
, A.CATEGORY
, SUM(PRICE * SALES) AS TOTAL_SALES
FROM BOOK A, AUTHOR B, BOOK_SALES C
WHERE A.AUTHOR_ID = B.AUTHOR_ID
AND A.BOOK_ID = C.BOOK_ID
AND TO_CHAR(SALES_DATE, 'YYYYMM') = '202201'
GROUP BY  A.AUTHOR_ID
        , B.AUTHOR_NAME
        , A.CATEGORY
ORDER BY AUTHOR_ID, CATEGORY DESC