📌[SQL 문제 풀이] leetcode - 1179. Reformat Department Table

💻 문제

✍️ 정답

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
;

🕶️ 해설

[ Group by - 전 ]

코드

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
FROM department
결과

id	Jan_Revenue	Feb_Revenue	Mar_Revenue
1	8000	null	null
2	9000	null	null
3	null	10000	null
1	null	7000	null
1	null	null	6000

[ Group by - 후 ]

코드

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
FROM department
GROUP BY id;
결과

id : 1 - Jan_Revenue 경우
(8000, null, null) 값이 그룹화 되는데 맨 앞에 있는 8000만 화면에 표시됩니다. 마찬가지로
id : 1 - Feb_Revenue 경우
( null, 7000, null) 값이 그룹화 되면서 맨 앞에 null 만 화면에 표시되는 거라고 볼 수 있겠네요.

id	Jan_Revenue	Feb_Revenue	Mar_Revenue
1	8000	null	null
2	9000	null	null
3	null	10000	null

이런 식으로 값이 나오기 때문에 집계함수를 통해 실제 존재하는 값을 조정해 주는 듯 하네요.