sql case to sum column values-CodePudding

I want to display the sum of amount fields based on filename column for each system. This is the query I tried:

SELECT 
    col1, 
    SUM(CASE WHEN filename LIKE '0701%' THEN (r_amt) ELSE 0 END) AS CW, 
    SUM(CASE WHEN filename LIKE '0801%' THEN (r_amt) ELSE 0 END) AS MIC, 
    SUM(CASE WHEN filename LIKE '0601%' THEN (r_amt) ELSE 0 END) AS CIC, 
    SUM(CASE WHEN filename LIKE '0401%' THEN (r_amt) ELSE 0 END) AS CIC,
FROM 
    table1 
GROUP BY col1;

and my result is this:

Col1    CW  MIC CIC CIC_1
--------------------------
Sys1    15  20  0   0
Sys2    0   0   50  0
Sys3    0   0   0   45
Sys4    0   0   0   0
Sys5    0   0   0   0

Question: I don't want the cic_1 in result, instead I want the sys 3 - CIC sum displayed in 3rd col like this:

Col1    CW  MIC CIC
----------------------
Sys1    15  20   0
Sys2     0   0  50
Sys3     0   0  45
Sys4     0   0   0
Sys5     0   0   0

CodePudding user response：

I think you just want this:

SELECT 
    col1, 
    SUM(CASE WHEN filename LIKE '0701%' THEN (r_amt) ELSE 0 END) AS CW, 
    SUM(CASE WHEN filename LIKE '0801%' THEN (r_amt) ELSE 0 END) AS MIC, 
    SUM(CASE WHEN filename LIKE '0601%' THEN (r_amt) 
             WHEN filename LIKE '0401%' THEN (r_amt)
             ELSE 0 END) AS CIC
FROM 
    table1 
GROUP BY col1;

Or this, if it makes more sense to you:

SELECT 
    col1, 
    SUM(CASE WHEN filename LIKE '0701%' THEN (r_amt) ELSE 0 END) AS CW, 
    SUM(CASE WHEN filename LIKE '0801%' THEN (r_amt) ELSE 0 END) AS MIC, 
    SUM(CASE WHEN (filename LIKE '0601%' OR filename like '0401%') THEN (r_amt) ELSE 0 END) AS CIC
FROM 
    table1 
GROUP BY col1;