[Leetcode] Reformat Department Table

문제 링크

정답

SELECT
    id,
    SUM(IF(month='Jan', revenue, null)) as Jan_Revenue,
    SUM(IF(month='Feb', revenue, null)) as Feb_Revenue,
    SUM(IF(month='Mar', revenue, null)) as Mar_Revenue,
    SUM(IF(month='Apr', revenue, null)) as Apr_Revenue,
    SUM(IF(month='May', revenue, null)) as May_Revenue,
    SUM(IF(month='Jun', revenue, null)) as Jun_Revenue,
    SUM(IF(month='Jul', revenue, null)) as Jul_Revenue,
    SUM(IF(month='Aug', revenue, null)) as Aug_Revenue,
    SUM(IF(month='Sep', revenue, null)) as Sep_Revenue,
    SUM(IF(month='Oct', revenue, null)) as Oct_Revenue,
    SUM(IF(month='Nov', revenue, null)) as Nov_Revenue,
    SUM(IF(month='Dec', revenue, null)) as Dec_Revenue
FROM Department
GROUP BY id

✅ Point

• SUM 안에 들어갈 수 있는 표현
  • column, column 끼리의 산수 연산, 상수(예를 들어 SUM(1): 총 행 수 세기), IF(), CASE WHEN, 표현식, …
• IF()는 조건이 단순할 때 더 간결하고 짧은 표현, CASE WHEN은 조건이 복잡하거나 여러 경우가 있는 상황에 더 유리하다.
• SUM(CASE WHEN month = 'Jan' THEN revenue ELSE NULL END) AS Jan_Revenue, 와 같이 case when으로도 풀 수 있음