How to Group summed columns with CASE in SELECT

Question

I have data that looks like this

Value | Type
----- | ------
100   | Travel
200   | G&A
100   | OH
300   | Travel

I want to sum it up like this:

TotalValue | Type
---------- | ------
400        | Travel
300        | Non-Travel

I wrote a query like this:

SELECT 
    SUM([Value]) as 'TotalValue',
    CASE [Type] WHEN 'Travel' THEN [Type] ELSE 'Non-Travel' END AS 'Type'
FROM 
    #tableName
GROUP BY 
    [Type]

However the result comes out as this instead:

TotalValue | Type
---------- | ------
400        | Travel
200        | Non-Travel
100        | Non-Travel

How can I fix this? Thanks in advance for your help!

Your ran into an order of operations issue. The FROM GROUP BY then SELECT are run. The alias 'type' is not in scope at the time the group by is executed. The values in [type] being grouped by are the type values in the database; not those re-evaluated by your case. Then the select runs and the case evaluates the G&A and OH to Non-Travel; so you think the engine isn't grouping correctly. — xQbert, Aug 09 '17 at 18:00

SqlZim · Accepted Answer · 2017-08-10T11:50:36.190

Use the expression you are using in your select in your group by as well:

SELECT 
    SUM([Value]) as TotalValue,
    CASE [Type] WHEN 'Travel' THEN [Type] ELSE 'Non-Travel' END AS Type
FROM 
    #tableName
GROUP BY 
    CASE [Type] WHEN 'Travel' THEN [Type] ELSE 'Non-Travel' END

rextester demo: http://rextester.com/YRN27346

returns:

+------------+------------+
| TotalValue |    Type    |
+------------+------------+
|        300 | Non-Travel |
|        400 | Travel     |
+------------+------------+

note: You should not to use single quotes around alias names, that is for string literals. If your alias names conflict with the rules for Database Identifiers then wrap them in square brackets instead.

How to Group summed columns with CASE in SELECT

1 Answers1