[프로그래머스/MySQL] lv.4 저자 별 카테고리 별 매출액 집계하기

문제 링크

[level 4] 저자 별 카테고리 별 매출액 집계하기 - 144856

 

Note

아... 너무 어려워서 다시 풀어봐야겠다.

 

Key

 

 

제출한 코드

조인하는 것부터 너무 골치가 아프다.

SELECT  BOOK.AUTHOR_ID, 
        AUTHOR.AUTHOR_NAME, 
        BOOK.CATEGORY, 
        BOOK_SALES.SALES,
        BOOK_SALES.BOOK_ID, 
        AUTHOR.AUTHOR_NAME
FROM    BOOK_SALES
INNER JOIN  BOOK
ON          BOOK_SALES.BOOK_ID = BOOK.BOOK_ID
INNER JOIN  AUTHOR
ON          BOOK.AUTHOR_ID = AUTHOR.AUTHOR_ID
GROUP BY BOOK_SALES.BOOK_ID
ORDER BY BOOK_SALES.BOOK_ID

일단 각각의 테이블끼리 조인

 

SELECT  BOOK.AUTHOR_ID, 
        AUTHOR.AUTHOR_NAME, 
        BOOK.CATEGORY, 
        (BOOK_SALES.SALES * BOOK.PRICE) AS TOTAL_SALES
FROM    BOOK_SALES
INNER JOIN  BOOK
ON          BOOK_SALES.BOOK_ID = BOOK.BOOK_ID
INNER JOIN  AUTHOR
ON          BOOK.AUTHOR_ID = AUTHOR.AUTHOR_ID
GROUP BY    BOOK.AUTHOR_ID,
            AUTHOR.AUTHOR_NAME,
            BOOK.CATEGORY
ORDER BY BOOK_SALES.BOOK_ID

다 했다고 생각했는데 틀림...

total sales는 group by를 해줬기 때문에 sum을 해줬어야했다.

 

SELECT  BOOK.AUTHOR_ID, 
        AUTHOR.AUTHOR_NAME, 
        BOOK.CATEGORY, 
        SUM(BOOK_SALES.SALES * BOOK.PRICE) AS TOTAL_SALES
FROM    BOOK_SALES
INNER JOIN  BOOK
ON          BOOK_SALES.BOOK_ID = BOOK.BOOK_ID
INNER JOIN  AUTHOR
ON          BOOK.AUTHOR_ID = AUTHOR.AUTHOR_ID
GROUP BY    BOOK.AUTHOR_ID,
            AUTHOR.AUTHOR_NAME,
            BOOK.CATEGORY
ORDER BY BOOK.AUTHOR_ID, BOOK.CATEGORY

정렬을 조건에 안 맞춰 준 것 같아서 다시 수정했는데도 틀림

SELECT  BOOK.AUTHOR_ID, 
        AUTHOR.AUTHOR_NAME, 
        BOOK.CATEGORY, 
        SUM(BOOK_SALES.SALES * BOOK.PRICE) AS TOTAL_SALES
FROM    BOOK_SALES
INNER JOIN  BOOK
ON          BOOK_SALES.BOOK_ID = BOOK.BOOK_ID
INNER JOIN  AUTHOR
ON          BOOK.AUTHOR_ID = AUTHOR.AUTHOR_ID
GROUP BY    BOOK.AUTHOR_ID,
            AUTHOR.AUTHOR_NAME,
            BOOK.CATEGORY
ORDER BY BOOK.AUTHOR_ID, BOOK.CATEGORY DESC

정렬 조건을 틀렸었다....

SELECT  BOOK.AUTHOR_ID, 
        AUTHOR.AUTHOR_NAME, 
        BOOK.CATEGORY, 
        SUM(BOOK_SALES.SALES * BOOK.PRICE) AS TOTAL_SALES
FROM    BOOK_SALES
INNER JOIN  BOOK
ON          BOOK_SALES.BOOK_ID = BOOK.BOOK_ID
INNER JOIN  AUTHOR
ON          BOOK.AUTHOR_ID = AUTHOR.AUTHOR_ID
WHERE   BOOK_SALES.SALES_DATE BETWEEN '2022-01-01' AND '2022-01-31'
GROUP BY    BOOK.AUTHOR_ID,
            AUTHOR.AUTHOR_NAME,
            BOOK.CATEGORY
ORDER BY BOOK.AUTHOR_ID, BOOK.CATEGORY DESC

최종... 도서 판매 데이터 범위 자체를 지정 안 해준거였다 ^.^