Conditional Group By Clause

[malxvc]

I have a query that displays sales totals by market:
Market (Group by), Sales (Sum), Goal (Sum)

How can I modify the query to display the results in a manner that groups the markets? For example, if the query returns results for six markets, and three of them are NY, NJ, and PA; how can I consolidate those three into a "new" market - 'Northeast'? Therefore, my query would only return four results, with NY, NJ and PA rolling up into 'Northeast'.

 

[ChiOscar]

Use this query sample:


SELECT
MARKET,
SUM(IIF(MARKET IN ('NY','NJ','PA'),Sales,0)) AS EastSales,
SUM(IIF(MARKET IN ('NY','NJ','PA'),Goals,0)) AS EastGoals
FROM
SampleTable
Group by
Market


Hope this helps.

 

[malxvc]

I knew I had to use the 'AS' operator, just didn't know how to approach the conditional part though. Thank you much.

 

[Pat Hartman]

I hate hard-coding values in queries and code. I would create a table that defines the groups and join to that table for more flexibility and easier modification.