Combine data with same ID

user_12424509 0
Viewed 205

I have an assignment where I need to use aggregate functions in the queries. I keep running into a problem where there are multiply entries for the same ID, and I would rather them be combined into one run (added together for the same ID).

-- Aggregate #3 - Show the total amount of pledges for each Team 
-- for particular year.   Order the results by amount of pledges highest 
-- to lowest.  This query should result in 1 row per Team.  
-- It should include the Team ID, Team name (or a combination of Sport, 
-- Level, and Gender), the total pledge amount and the Event date/year.

SELECT DISTINCT SUM(TEGS.monPledgeAmount) AS TotalPledge
  ,TE.intEventID
  ,TTC.intTeamandClubID
  ,TE.dtmEventDate
  ,TGS.strGenderDesc
  ,TLT.strLevelDesc
FROM TEventGolfers AS TEG JOIN TEvents TE
    ON TEG.intEventID = TE.intEventID

JOIN TGolfers AS TG
    ON TG.intGenderID = TEG.intGolferID

JOIN TEventGolferSponsors AS TEGS
    ON TEGS.intEventGolferID = TEG.intEventGolferID

JOIN TEventGolferTeamandClubs AS TEGTC
    ON TEGTC.intEventGolferID = TEG.intEventGolferID

JOIN TTeamandClubs AS TTC
    ON TTC.intTeamandClubID = TEGTC.intTeamandClubID

JOIN TLevelofTeams AS TLT
    ON TLT.intLevelofTeamID = TTC.intLevelofTeamID

JOIN TGenders AS TGS
    ON TGS.intGenderID = TTC.intGenderID


GROUP BY
    TEGS.monPledgeAmount
   ,TE.intEventID
   ,TTC.intTeamandClubID
   ,TE.dtmEventDate
   ,TGS.strGenderDesc
   ,TLT.strLevelDesc

Output below (IDs to be combined in column 3, "intTeamandClubID"):

0.80    2   3   2016-01-01  Female  Varsity Football
0.80    2   4   2016-01-01  Male    Varsity Golf
4.00    2   3   2016-01-01  Female  Varsity Football
4.00    2   4   2016-01-01  Male    Varsity Golf
10.00   2   3   2016-01-01  Female  Varsity Football
50.00   2   3   2016-01-01  Female  Varsity Football

I want the "intTeamandClubID" to be 1 row for the same ID, and the "TotalPledge" to be added together.

sql-server sql
2 Answers

You can remove the DISTINCT in the SELECT clause and the column TEGS.monPledgeAmount in the GROUP BY clause.

SELECT SUM(TEGS.monPledgeAmount) AS TotalPledge
  ,TE.intEventID
  ,TTC.intTeamandClubID
  ,TE.dtmEventDate
  ,TGS.strGenderDesc
  ,TLT.strLevelDesc
FROM TEventGolfers AS TEG JOIN TEvents TE
    ON TEG.intEventID = TE.intEventID

JOIN TGolfers AS TG
    ON TG.intGenderID = TEG.intGolferID

JOIN TEventGolferSponsors AS TEGS
    ON TEGS.intEventGolferID = TEG.intEventGolferID

JOIN TEventGolferTeamandClubs AS TEGTC
    ON TEGTC.intEventGolferID = TEG.intEventGolferID

JOIN TTeamandClubs AS TTC
    ON TTC.intTeamandClubID = TEGTC.intTeamandClubID

JOIN TLevelofTeams AS TLT
    ON TLT.intLevelofTeamID = TTC.intLevelofTeamID

JOIN TGenders AS TGS
    ON TGS.intGenderID = TTC.intGenderID


GROUP BY
    
   TE.intEventID
   ,TTC.intTeamandClubID
   ,TE.dtmEventDate
   ,TGS.strGenderDesc
   ,TLT.strLevelDesc
user_148918130

You want to sum the TotalPledge values for each intTeamandClubID. For that you would need to create a different group for each intTeamandClubID. In your case, since you are selecting multiple columns, you will create a group for each unique combination of those columns, which is done by a GROUP BY statement.

You already have that in your query, but you are also grouping by TotalPledge, which you don't want. You want to SUM that value, so you should remove it from the GROUP BY:

GROUP BY
   TE.intEventID
   ,TTC.intTeamandClubID
   ,TE.dtmEventDate
   ,TGS.strGenderDesc
   ,TLT.strLevelDesc

Note that, even though it has some use-cases alongside GROUP BY, DISTINCT is unnecessary here since each group (combination of columns values) is unique. Leaving it in the query, however, won't impact the result

user_121622430
Related