How to sum a value if both lines are equal?

Question

Here is my request

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 

Result

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

I want the result to sum StateDuration per day something like this

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

Thanks for the help.

Answer 1

Use GROUP BY UserId , so rows with the same user ID are grouped. The remaining columns of the SELECT list should use aggregate functions. You want SUM() for StateDuration and probably MIN() or MAX() (or both!) For StatusDateTime .

You can also use aliases to improve readability:

 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 ;