Do I need to perform a Join in order to merge multiple columns with 0 in them

Question

I may have asked this question incorrectly but I have the following query where I'm trying to pull 3 different metrics for a given store. When I pull them though it comes out as below:

Is there a way I could get all of these so that the Generated, Post, and Daily Percentages are all on one line? I would want it to look something like this

This is the query I used for it:

SELECT
                        STORE_NUM,                  
                        --REPORT_TYPE,      
                        REPORT_STARTDATE,       
                        --GEN_IMPORT_DT,        
                        --POST_IMPORT_DT,       
                        --SID,      
                        --REQ,
                    
                        CASE WHEN REPORT_TYPE = 'G' THEN SE_PCNT ELSE 0 END AS SE_PCNT_Generated,
                        CASE WHEN REPORT_TYPE = 'A' THEN SE_PCNT ELSE 0 END AS SE_PCNT_At_Post,
                        CASE WHEN REPORT_TYPE = 'D' THEN SE_PCNT ELSE 0 END AS SE_PCNT_Daily
            FROM (
            
            SELECT  STORE_NUM,                  
                    REPORT_TYPE,        
                    REPORT_STARTDATE,       
                    DATE_IMPORT_2 AS GEN_IMPORT_DT,     
                    DATE_IMPORT_3 AS POST_IMPORT_DT,        
                    SUM (COVERAGE_EFFECTIVE_SCHED_DEMAND) AS SID,       
                    SUM (COVERAGE_EFFECTIVE_REQUIRED) AS REQ,       
                    CASE WHEN SUM (COVERAGE_EFFECTIVE_SCHED_DEMAND)>0 THEN      
                            SUM (COVERAGE_EFFECTIVE_SCHED_DEMAND) / SUM(COVERAGE_EFFECTIVE_REQUIRED) ELSE 0 END AS SE_PCNT                      
            FROM DNA_DM..STOREOPS_SCHEDULE_EFFECTIVENESS_RPT                
            WHERE
            --REPORT_TYPE = 'A' AND REC_TYPE = 'S'              
            REPORT_STARTDATE >='03/08/21'
            AND STORE_NUM = 2
            GROUP BY 1, 2, 3, 4, 5  
            
            ) AS q

Caius Jard · Answer 1 · 2021-03-18T18:30:40.620

Just add a SUM(..) around every one of your CASE WHENs and add a GROUP BY STORE_NUM, REPORT_STARTDATE after q

ps; you don't need the ELSE 0 - if you omit it then the rows that are currently bearing 0 will be NULL instead; NULL doesn't participate in aggregations, so it makes it easier to use something like MAX instead of SUM to "get the only non null value in the column" when the group by is performing its work of "squishing your 3 rows into 1"

As such, for future I recommend the more general pattern of:

SELECT 
  x, y,
  MAX(CASE WHEN ... THEN ... END) as ... --do not use ELSE here!
FROM
  ...
GROUP BY
  x, y

This form of query works well for situations where the CASE WHEN is picking strings, dates, or other things that can't be summed (and it works well for situations like yours where if it was just one numeric value in a column of nulls) - all in, it's more flexible than using SUM(CASE WHEN ... THEN ... ELSE 0 END)

score 0 · Accepted Answer · answered Mar 18 '21 at 18:25

SELECT
                        STORE_NUM,                  
                        max(REPORT_STARTDATE),                               
                        max(CASE WHEN REPORT_TYPE = 'G' THEN SE_PCNT ELSE 0 END )AS SE_PCNT_Generated,
                        max(CASE WHEN REPORT_TYPE = 'A' THEN SE_PCNT ELSE 0 END )AS SE_PCNT_At_Post,
                        max(CASE WHEN REPORT_TYPE = 'D' THEN SE_PCNT ELSE 0 END )AS SE_PCNT_Daily
            FROM (
            
            SELECT  STORE_NUM,                  
                    REPORT_TYPE,        
                    REPORT_STARTDATE,       
                    DATE_IMPORT_2 AS GEN_IMPORT_DT,     
                    DATE_IMPORT_3 AS POST_IMPORT_DT,        
                    SUM (COVERAGE_EFFECTIVE_SCHED_DEMAND) AS SID,       
                    SUM (COVERAGE_EFFECTIVE_REQUIRED) AS REQ,       
                    CASE WHEN SUM (COVERAGE_EFFECTIVE_SCHED_DEMAND)>0 THEN      
                            SUM (COVERAGE_EFFECTIVE_SCHED_DEMAND) / SUM(COVERAGE_EFFECTIVE_REQUIRED) ELSE 0 END AS SE_PCNT                      
            FROM DNA_DM..STOREOPS_SCHEDULE_EFFECTIVENESS_RPT                
            WHERE
            --REPORT_TYPE = 'A' AND REC_TYPE = 'S'              
            REPORT_STARTDATE >='03/08/21'
            AND STORE_NUM = 2
            GROUP BY 1, 2, 3, 4, 5  
            
            ) AS q
group by store_num

If you want the value of SE_PCNT_Generated, SE_PCNT_At_Post and SE_PCNT_Daily store wise and report date wise then use :

SELECT
                            STORE_NUM,                  
                            REPORT_STARTDATE,                               
                            max(CASE WHEN REPORT_TYPE = 'G' THEN SE_PCNT ELSE 0 END )AS SE_PCNT_Generated,
                            max(CASE WHEN REPORT_TYPE = 'A' THEN SE_PCNT ELSE 0 END )AS SE_PCNT_At_Post,
                            max(CASE WHEN REPORT_TYPE = 'D' THEN SE_PCNT ELSE 0 END )AS SE_PCNT_Daily
                FROM (
                
                SELECT  STORE_NUM,                  
                        REPORT_TYPE,        
                        REPORT_STARTDATE,       
                        DATE_IMPORT_2 AS GEN_IMPORT_DT,     
                        DATE_IMPORT_3 AS POST_IMPORT_DT,        
                        SUM (COVERAGE_EFFECTIVE_SCHED_DEMAND) AS SID,       
                        SUM (COVERAGE_EFFECTIVE_REQUIRED) AS REQ,       
                        CASE WHEN SUM (COVERAGE_EFFECTIVE_SCHED_DEMAND)>0 THEN      
                                SUM (COVERAGE_EFFECTIVE_SCHED_DEMAND) / SUM(COVERAGE_EFFECTIVE_REQUIRED) ELSE 0 END AS SE_PCNT                      
                FROM DNA_DM..STOREOPS_SCHEDULE_EFFECTIVENESS_RPT                
                WHERE
                --REPORT_TYPE = 'A' AND REC_TYPE = 'S'              
                REPORT_STARTDATE >='03/08/21'
                AND STORE_NUM = 2
                GROUP BY 1, 2, 3, 4, 5  
                
                ) AS q
    group by store_num,REPORT_STARTDATE

This was just about what I needed however it only returns the max date which is 3/29, I would need the date for the report so in this case 3/08/2021 — adura826, Mar 18 '21 at 18:29
You are most welcome. Since we are using max so you can omit else 0 if you want. Please accept this answer if it helped you. — Kazi Mohammad Ali Nur Romel, Mar 18 '21 at 18:33
I'll note the else 0 for future cases. Would you mind explaining why Max was used on the Case When's? Also, answer was accepted — adura826, Mar 18 '21 at 18:52

Do I need to perform a Join in order to merge multiple columns with 0 in them

2 Answers2