Group By and Groups of Groups in MS Access

I am trying to write a query in MS Access using group by that results in the count of two groups and then the count of the remaining groups combined.

So if I have a field color and 18 items and I do a count of items group by color I would have the following results:

red = 6
green = 3
orange = 2
blue = 3
yellow = 4 

However, what I’m looking for is the following:

red = 6
green = 3
All other colors = 9

Not sure how to code the sql for this.

Please advise.

Thanks

Answer

Consider:

SELECT Count(*) AS CntColor, Switch([Color]="Red","Red",[Color]="Green","Green",True,"Other") AS ColorGrp
FROM Table1
GROUP BY Switch([Color]="Red","Red",[Color]="Green","Green",True,"Other");