Custom Grouping multiple values 1

[briesen]

I'm doing an SQL query where my first field has six different values.

Theoretically, they are Blue, Green, Red, Yellow, Orange, Purple

Is it possible to force them to group two or more colors together?

For example, I want Blue and Green to group together, Red and Yellow to Group together, and Orange and Purple to group together so the aggregate values add up in each group.

I tried this, but it still keeps them separate if I put an alias so I know it doesn't work.

SELECT
CASE
WHEN vtr.Color IN ('Blue', 'Green') THEN 'A' 
WHEN vtr.Color IN ('Red', 'Yellow') THEN 'B'
WHEN vtr.Color IN ('Orange', 'Purple') THEN 'C'
ELSE vtr.Color END AS [Color]

Thanks!

 

[RiverGuy]

Use a derived table, or repeat your case statement in the GROUP BY clause. Here's a derived table example.

SELECT [Color],
SUM(SomeValueColumnExample) AS TheValue
FROM

(SELECT
CASE
WHEN vtr.Color IN ('Blue', 'Green') THEN 'A'
WHEN vtr.Color IN ('Red', 'Yellow') THEN 'B'
WHEN vtr.Color IN ('Orange', 'Purple') THEN 'C'
ELSE vtr.Color END AS [Color],
SomeValueColumnExample
FROM YourTableExample) x

GROUP BY [Color]