Combine data with same ID

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.

Answer

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