Group by a result of Case Substring SQL Query

October 4, 2022

I am having this query below and am unable to get the result of the full quantity when I use group by

SELECT CASE SUBSTRING(Data_Code, 3,1) 
         WHEN '1' THEN 'SS' 
         ELSE 'FW' 
       END + SUBSTRING(Data_Code, 1,2) [MyNewColumn]
SUM(QUANTITY) 
FROM [MyTable]
GROUP BY Data_Code

below is the sample of the table

|DataCode|Quantity|
|22263738| 10     |
|22251616| 15     |

am expecting to have a result of 25

Looking forwards to your kind help

Thanks

>Solution :

Just use a sub-query

SELECT
    MyNewColumn
    , SUM(QUANTITY) 
FROM (
    SELECT
        QUANTITY
        , CASE SUBSTRING(Data_Code, 3,1) 
        WHEN '1' THEN 'SS' 
        ELSE 'FW'
        END + SUBSTRING(Data_Code, 1,2) MyNewColumn
    FROM MyTable
) X
GROUP BY MyNewColumn;