Slightly different flavor with the addition of "valid groups" instead of hard coding which values to combine

😎

USE TEEST;

GO

SET NOCOUNT ON;

;WITH SAMPLE_DATA(StateID,DistrictID,CityID,Grp,VoteCount) AS

(

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

)

,VALID_GROUPS(Grp) AS

(

SELECT CONVERT(VARCHAR(16),'Abc',0) UNION ALL

SELECT CONVERT(VARCHAR(16),'Bcd',0) UNION ALL

SELECT CONVERT(VARCHAR(16),'Cde',0)

)

,BASE_DATA AS

(

SELECT

SD.StateID

,SD.DistrictID

,SD.CityID

,ISNULL(VG.Grp,'Other') AS Grp

,SD.VoteCount

FROMSAMPLE_DATASD

LEFT OUTER JOIN VALID_GROUPSVG

ONSD.Grp=VG.Grp

)

SELECT

BD.StateID

,BD.DistrictID

,BD.CityID

,BD.Grp

,SUM(BD.VoteCount) AS VoteCount

FROMBASE_DATABD

GROUP BY BD.StateID

,BD.DistrictID

,BD.CityID

,BD.Grp;

Output

StateID DistrictID CityID Grp VoteCount

----------- ----------- ----------- ---------------- -----------

1 1 1 Abc 10

1 1 2 Bcd 10

1 2 1 Cde 10

2 1 2 Other 14