我有一个任务,需要在查询中使用聚合函数.我一直遇到一个问题,同一个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"是加在一起的.

推荐答案

您可以删除SELECT子句中的DISTINCTGROUP BY子句中的TEGS.monPledgeAmount列.

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

Sql相关问答推荐

在甲骨文中查找前一个星期一的S日期

retrofit AWS Athena中的JSON

Django将字符串筛选为整数?

编写一个SQL查询来返回收视率较高的类型,并取这些收视率的平均值,我该如何做呢?

在Oracle SQL中将列值转换为行

如何将`now()`作为SQL插入语句的一部分?

正在编写查询.我需要将订阅的时间段分为第一个订阅中包含的另一个订阅之前和之后的时间段

仅当交叉应用返回单值时才更新记录

如何在Postgres中为单值输入多行?

此过程如何在不引用传递的参数值的情况下执行工作?

如何使子查询在UPDATE语句期间获得最新更新

根据日期 Select ID 的上一条记录

在同一列上迭代时计算持续时间

在 SQL 中使用循环遍历按时间顺序排列的数据

为每组填写行以进行旋转

如何根据 Amazon Athena 中的多个列值删除重复行?

如何在 SQL Server 中参数化 Select top 'n'

Lag() 获取snowflake的值变化

如何列出 Oracle 数据库中的所有函数并按修改日期排序

SQL - 汇总多个表的结果