July 30, 2016 at 5:31 pm
Hi team
I have a requirement...below scenario
Stateid Districtid cityid group votecount
1. 1. 1. Abc 10
1. 1. 2. Bcd 10
1. 2. 1. Cde 10
2. 1. 2. Xyz 5
2. 1. 2. Wxy 6
2. 1. 2. Vwx 3
Result set should be like below:
1. 1. 1. Abc 10
1. 1. 2. Bcd 10
1. 2. 1. Cde 10
2. 1. 2. Othrs14
Last 3 groups should be viewed as Othrs and count should sum up.
Any help would be appreciated.
Thank you.....
July 30, 2016 at 6:55 pm
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;
July 31, 2016 at 1:43 am
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
August 1, 2016 at 1:24 am
Apologies for the format....I couldn't explain in detail..
Thank You for the replies....
August 1, 2016 at 1:28 am
sqlquery29 (8/1/2016)
Apologies for the format....I couldn't explain in detail..Thank You for the replies....
You are very welcome and thanks for the feedback.
😎
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply