리트코드 [Reformat Department Table]

문제

https://leetcode.com/problems/reformat-department-table/description/

각 department id별 월(month) 수익(revenue)의 총합을 계산해 보자.

Table: Department

Example1

문제풀이

• id가 3번 나타나는 경우가 있기 때문에 GROUP BY를 사용함으로써 집계함수를 필요함

• GROUP BY로 id를 묶어줄 것이기 때문에, SELECT 다음에 id를 먼저 써주어야 한다.

쿼리

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