mysql - 如果两行相等，如何对值求和？

Question

这是我的查询

Select 
    AgentActivityLog.StatusDateTime, 
    AgentActivityLog.UserId, 
    AgentActivityLog.StateDuration /60 AS Minutes, 
    AgentActivityLog.StatusKey
FROM UserWorkgroups 
INNER JOIN AgentActivityLog ON UserWorkgroups.UserId = AgentActivityLog.UserId 
WHERE 
   AgentActivityLog.StatusDateTime Between '08/01/2013' AND '08/02/2013' AND 
   StatusKey IN ('Shadow Trainer') AND
    (UserWorkgroups.WorkGroup in ('LV_GR_9','LV_CCS_GENERAL','LV_PBX_INTERNAL'))
ORDER BY 
    AgentActivityLog.StatusDateTime, 
    AgentActivityLog.UserId, 
    AgentActivityLog.StatusKey, 
    AgentActivityLog.StateDuration

结果

2013-08-01 08:59:03.000 JMENDOZA1   13  Shadow Trainer
2013-08-01 09:13:50.000 JMENDOZA1   12  Shadow Trainer

我希望结果对当天的 StateDuration 求和像这样

2013-08-01 08:59:03.000 JMENDOZA1 25    Shadow Trainer.

谢谢你的帮助。

Answer 1

使用 GROUP BY UserId，所以具有相同用户 ID 的行被分组。在 SELECT 列表的其余列中，必须应用聚合函数。您需要 SUM() 作为 StateDuration 并且可能需要 MIN() 或 MAX()(或两者!)对于 StatusDateTime。

您还可以使用别名来提高可读性:

SELECT 
    MIN(aal.StatusDateTime) AS MinStatusDateTime, 
    MAX(aal.StatusDateTime) AS MaxStatusDateTime, 
    aal.UserId, 
    SUM(aal.StateDuration / 60) AS Minutes, 
    'Shadow Trainer' AS StatusKey
FROM UserWorkgroups AS uw
  INNER JOIN AgentActivityLog AS aal ON uw.UserId = aal.UserId 
WHERE 
    aal.StatusDateTime BETWEEN '2013-08-01' AND '2013-08-02' AND 
    aal.StatusKey IN ('Shadow Trainer') AND
    uw.WorkGroup IN ('LV_GR_9','LV_CCS_GENERAL','LV_PBX_INTERNAL')
GROUP BY 
    aal.UserId
ORDER BY 
    MinStatusDateTime, 
    UserId, 
    StatusKey, 
    Minutes ;