Hi - I have a a Group By query which is working fine aggregating records by city.  Now I have a requirement to focus on one city and then group the other cities to 'Other'.  Here is the query which works:

Select [City]= CASE WHEN [City] = 'St. Louis' THEN 'St. Louis' ELSE 'Other Missouri City' END, SUM([Cars]) AS 'Total Cars' 
From [Output-MarketAnalysis]
Where [City] IN ('St. Louis','Kansas City','Columbia', 'Jefferson City','Joplin') AND [Status] = 'Active'
Group by [City]

Here is the result:

St. Louis	1000
Kansas City	800
Columbia	700
Jefferson City	650
Joplin	300

When I add this Case When statement to roll up the city information it changes the name of the city to 'Other Missouri City' however it does not aggregate all Cities with the value 'Other Missouri City':

Select [City]= CASE WHEN [City] = 'St. Louis' THEN 'St. Louis' ELSE 'Other Missouri City' END, SUM([Cars]) AS 'Total Cars' 
From [Output-MarketAnalysis]
Where [City] IN ('St. Louis','Kansas City','Columbia', 'Jefferson City','Joplin') AND [Status] = 'Active'
Group by [City]

Here is the result:

St. Louis	1000
Other Missouri City	800
Other Missouri City	700
Other Missouri City	650
Other Missouri City	300

What I would like to see is a result like:

St. Louis	1000
Other Missouri City	2450

Any help would be greatly appreciated as I have tried everything I could find online.

Select [City]= CASE WHEN [City] = 'St. Louis' THEN 'St. Louis' ELSE 'Other Missouri City' END, SUM([Cars]) AS 'Total Cars' 
From [Output-MarketAnalysis]
Where [City] IN ('St. Louis','Kansas City','Columbia', 'Jefferson City','Joplin') AND [Status] = 'Active'
Group by CASE WHEN [City] = 'St. Louis' THEN 'St. Louis' ELSE 'Other Missouri City' END

• Proposed as answer by Naomi NModerator 14 hours 46 minutes ago
• Marked as answer by Fanuke 14 hours 11 minutes ago

Thanks for the quick response!  It worked.  I tried something similar but included [City]=... in the Group by statement as well, which wasn't working.  What is the right format for repeating a function in the Group by statement from the select statement?  I have another column to add to this query which still doesn't work.  I am trying to aggregate roll dates up to a month and then aggregate the total by month so the same query looks like this using your input from before (the new part is in bold):

Select [City]= CASE WHEN [City] = 'St. Louis' THEN 'St. Louis' ELSE 'Other Missouri City' END, SUM([Cars]) AS 'Total Cars',  DATEADD(month, DATEDIFF(month, 0, [Inventory Date]), 0) AS 'Inventory Date'
From [Output-MarketAnalysis]
Where [City] IN ('St. Louis','Kansas City','Columbia', 'Jefferson City','Joplin') AND [Status] = 'Active'
Group by CASE WHEN [City] = 'St. Louis' THEN 'St. Louis' ELSE 'Other Missouri City' END, DATEADD(month, DATEDIFF(month, 0, [Inventory Date]), 0) AS 'Inventory Date'

This version creates a syntax error. Just including [Inventory Date] in the Group By statement leads to the same problem I had before, it doesn't aggregate by common values.  Thanks so much for your help!!

Remove AS part from the GROUP BY.

There are a few approaches to avoid repeating the same expression.

1. Use derived table or CTE, e.g.

;with cte as (Select [City]= CASE WHEN [City] = 'St. Louis' THEN 'St. Louis' ELSE 'Other Missouri City' END, SUM([Cars]) AS 'Total Cars',  DATEADD(month, DATEDIFF(month, 0, [Inventory Date]), 0) AS 'Inventory Date'
From [Output-MarketAnalysis]
Where [City] IN ('St. Louis','Kansas City','Columbia', 'Jefferson City','Joplin') AND [Status] = 'Active')

select City, InventoryDate from cte

GROUP BY City, InventoryDate

2. Using CROSS APPLY to create computed columns

3. Repeating the same expressions twice (but in the GROUP BY without the AS keyword for the column name) 

Thank you, again that worked and the explanation was really helpful!