[LeetCode 연습문제] Reformat Department Table

Reformat Department Table

+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| revenue | int |
| month | varchar |
+-------------+---------+
(id, month) is the primary key of this table.
The table has information about the revenue of each department per month.
The month has values in ["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"].

Write an SQL query to reformat the table such that there is a department id column and a revenue column for each month.

Return the result table in any order.

[문제에서 구해야하는 것]
1.부서 ID 열 (이후 그룹화 할 기준)
2)달 별 수입 열

[틀린 쿼리]

# Write your MySQL query statement below
SELECT id
       ,(CASE WHEN month="Jan" THEN revenue ELSE NULL END) AS 'Jan_Revenue' 
       ,(CASE WHEN month="Feb" THEN revenue ELSE NULL END) AS 'Feb_Revenue' 
       ,(CASE WHEN month="Mar" THEN revenue ELSE NULL END) AS 'Mar_Revenue' 
       ,(CASE WHEN month="Apr" THEN revenue ELSE NULL END) AS 'Apr_Revenue' 
       ,(CASE WHEN month="May" THEN revenue ELSE NULL END) AS 'May_Revenue' 
       ,(CASE WHEN month="Jun" THEN revenue ELSE NULL END) AS 'Jun_Revenue' 
       ,(CASE WHEN month="Jul" THEN revenue ELSE NULL END) AS 'Jul_Revenue' 
       ,(CASE WHEN month="Aug" THEN revenue ELSE NULL END) AS 'Aug_Revenue' 
       ,(CASE WHEN month="Sep" THEN revenue ELSE NULL END) AS 'Sep_Revenue' 
       ,(CASE WHEN month="Oct" THEN revenue ELSE NULL END) AS 'Oct_Revenue' 
       ,(CASE WHEN month="Nov" THEN revenue ELSE NULL END) AS 'Nov_Revenue' 
       ,(CASE WHEN month="Dec" THEN revenue ELSE NULL END) AS 'Dec_Revenue'                    
FROM Department           

틀린 이유
1)단순히 값을 나열했을 뿐, 월 별로 GROUPING을 해주지 않았다
2)본 문제는 월마다 벌었던 수익의 합을 구해야 함으로 SUM()을 활용해야한다

[정답]

SELECT id
       ,SUM(CASE WHEN month="Jan" THEN revenue ELSE NULL END) AS 'Jan_Revenue' 
       ,SUM(CASE WHEN month="Feb" THEN revenue ELSE NULL END) AS 'Feb_Revenue' 
       ,SUM(CASE WHEN month="Mar" THEN revenue ELSE NULL END) AS 'Mar_Revenue' 
       ,SUM(CASE WHEN month="Apr" THEN revenue ELSE NULL END) AS 'Apr_Revenue' 
       ,SUM(CASE WHEN month="May" THEN revenue ELSE NULL END) AS 'May_Revenue' 
       ,SUM(CASE WHEN month="Jun" THEN revenue ELSE NULL END) AS 'Jun_Revenue' 
       ,SUM(CASE WHEN month="Jul" THEN revenue ELSE NULL END) AS 'Jul_Revenue' 
       ,SUM(CASE WHEN month="Aug" THEN revenue ELSE NULL END) AS 'Aug_Revenue' 
       ,SUM(CASE WHEN month="Sep" THEN revenue ELSE NULL END) AS 'Sep_Revenue' 
       ,SUM(CASE WHEN month="Oct" THEN revenue ELSE NULL END) AS 'Oct_Revenue' 
       ,SUM(CASE WHEN month="Nov" THEN revenue ELSE NULL END) AS 'Nov_Revenue' 
       ,SUM(CASE WHEN month="Dec" THEN revenue ELSE NULL END) AS 'Dec_Revenue'                    
FROM Department 
GROUP BY id

#본 내용은 데이터리안 'SQL 데이터 분석 캠프 입문반'을 수강하며 작성한 내용입니다