Since you're pretty new here, I just answered your question... but you really should make it as easy as possible for folks to help you. Here[/url] is a good article explaining how to ask a question that will get answered.

Now that that's out of the way...

This is one way to solve it

SELECT y.StateID

, y.DistrictID

, y.StateID

, y.NewGroup

, SUM(VoteCount) AS TotalVotes

FROM (

SELECT x.StateID

, x.DistrictID

, x.CityID

, x.Grp

, CASE WHEN x.Grp IN ('Xyz','Wxy','Vwx') THEN 'Other' ELSE x.Grp END NewGroup

, x.VoteCount

FROM

(SELECT 1 AS StateID, 1 AS DistrictID, 1 AS CityID, 'Abc' As Grp, 10 AS VoteCount

UNION ALL SELECT 1, 1, 2, 'Bcd', 10

UNION ALL SELECT 1, 2, 1, 'Cde', 10

UNION ALL SELECT 2, 1, 2, 'Xyz', 5

UNION ALL SELECT 2, 1, 2, 'Wxy', 6

UNION ALL SELECT 2, 1, 2, 'Vwx', 3) x ) y

GROUP BY y.StateID

, y.DistrictID

, y.NewGroup;