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

https://school.programmers.co.kr/learn/courses/30/lessons/144856

프로그래머스

정답

SELECT A.AUTHOR_ID,B.AUTHOR_NAME,A.CATEGORY,SUM(C.SALES * PRICE) TOTAL_SALES
FROM BOOK A
    INNER JOIN AUTHOR B
    ON A.AUTHOR_ID = B.AUTHOR_ID
    INNER JOIN BOOK_SALES C
    ON A.BOOK_ID = C.BOOK_ID
WHERE TO_CHAR(C.SALES_DATE,'YYYY') ='2022'
    AND TO_CHAR(C.SALES_DATE,'MM') ='01'
GROUP BY A.AUTHOR_ID,A.CATEGORY,B.AUTHOR_NAME
ORDER BY A.AUTHOR_ID,CATEGORY DESC
;

풀이

Step 1) 2022년 1월 where 절 조건 걸기

• 3개 테이블 INNER JOIN 
• WHERE TO_CHAR(C.SALES_DATE,'YYYY') ='2022'
    AND TO_CHAR(C.SALES_DATE,'MM') ='01'
  ⇒ 2022년 1월

SELECT *
FROM BOOK A
    INNER JOIN AUTHOR B
    ON A.AUTHOR_ID = B.AUTHOR_ID
    INNER JOIN BOOK_SALES C
    ON A.BOOK_ID = C.BOOK_ID
WHERE TO_CHAR(C.SALES_DATE,'YYYY') ='2022'
    AND TO_CHAR(C.SALES_DATE,'MM') ='01'
;

Step 2) Group by

• SELECT 절에 컬럼명 중복 되지 않으면 컬럼명만 작성 가능하다(테이블 생략)

SELECT A.AUTHOR_ID,AUTHOR_NAME,CATEGORY,SUM(C.SALES * PRICE) TOTAL_SALES
FROM BOOK A
    INNER JOIN AUTHOR B
    ON A.AUTHOR_ID = B.AUTHOR_ID
    INNER JOIN BOOK_SALES C
    ON A.BOOK_ID = C.BOOK_ID
WHERE TO_CHAR(C.SALES_DATE,'YYYY') ='2022'
    AND TO_CHAR(C.SALES_DATE,'MM') ='01'
GROUP BY A.AUTHOR_ID,A.CATEGORY,B.AUTHOR_NAME
ORDER BY A.AUTHOR_ID,CATEGORY DESC
;