Reformat Department Table || Leetcode SQL solution || easy & simplest solution || ✅✅✅💯💯💯🔥🔥🔥

                     1179. Reformat Department Table

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.

The query result format is in the following example.

Detailed Solution:

# Write your MySQL query statement below
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; 

Approach:

            To write a query for the department table such that table contains 

                    1. department id column 

                    2. revenue columns for each month 

so, we need to have total of 13 columns such that 

1st column ->      department id 

12 columns ->     Jan_Revenue, Feb_Revenue, Mar_Revenue, Apr_Revenue, May_Revenue, Jun_Revenue, Jul_Revenue, Aug_Revenue, Sep_Revenue, Oct_Revenue, Nov_Revenue, Dec_Revenue 

    

        group the elements by id, then, 

        Aggregation is perfomed on columns by sum condition and by checking if condition on            that id  

IF()

IF(condition, if_true, if_false)
    condition - If month is equal to the respective column (for Jan_revenue column it should be month = 'Jan')
if_true= return all the list of values with respect to the individual id, afterwards we will sum all the values in the next step.
if_false - Just return NULL id there is no revenue with respect to that ID and the month.

SUM()
We sum all the values of respective id per month and then return it in the front of that id in the respective month column.
SUM(will sum all the values under the condition in this bracket)

STEP 2:

        First Group by action is perfomed, then aggregation is perfomed on columns, having sum and if. 

Simple query 

For Jan_Revenue:

# Write your MySQL query statement below
select id, 
        sum(
            if(month = "Jan",revenue,null)
            ) as Jan_Revenue

from Department 
group by id; 

                                

                                HAPPY LEARNING :) 

for leetcode solution : https://leetcode.com/problems/reformat-department-table/solutions/3448665/easy-sql-query-beginner-level-using-if-simplest-solution/