我有一个任务,需要在查询中使用聚合函数.我一直遇到一个问题,同一个ID有多个条目,我宁愿将它们合并到一个运行中(为同一个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
以下输出(ID将在第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
我希望"IntTeamAndClubBid"是同一ID的1行,而"TotalCredit"是加在一起的.