CODEWITHSUNDEEP
sqlms-accessgroup-by
Windstorm1981
Windstorm1981

Reputation: 2680

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

Upvotes: 0

Views: 146

Answers (1)

June7
June7

Reputation: 21370

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");

Upvotes: 1

Related Questions