Leetcode - 1179. Reformat Department Table(헤맴)

문제

Table: Department

+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| revenue | int |
| month | varchar |
+-------------+---------+
In SQL,(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"].

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.

The result format is in the following example.

풀이

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

배운점

• 처음엔 CASE문 앞에 SUM()을 안 썼다. 그랬더니 쿼리가 제대로 안 나옴
  만약 여러 월의 매출을 함께 보고 싶다면, 각 월에 대한 조건을 모두 적용한 후, 그 값을 합산하는 것이 필요. 즉, SUM()적용 해야함.
• 처음에 GROUP BY도 안씀. ID별로 매출보려면 GROUP BY id필요!