SQL Grouping

• caldrumr1234

  Mr or Mrs. 500

  Points: 562

  More actions

  December 27, 2017 at 5:57 pm

  #325272

  I am having some issues with this.
  There must be something I am formatting incorrectly.
  I tried summing it in the outer query and the inner, the results are line by line (not summed).
  Any ideas?

  SELECT s.AgentName
    , s.ReasonCode
    , Sum(s.StateDuration) AS 'StateDuration'
FROM (
    SELECT [Agent Name] AS 'AgentName'
        , [Reason Code] AS 'ReasonCode'
        , Cast(Datepart(Second, [Duration]) AS INT) + Cast(60 * Datepart(Minute, [Duration]) AS INT) + Cast(3600 * Datepart(Hour, [Duration]) AS INT) AS 'StateDuration'
    FROM State_Details
    GROUP BY [Agent Name]
        , [Reason Code]
        , [Duration]
    ) s
GROUP BY s.AgentName
    , s.ReasonCode
    , s.StateDuration

• pietlinden

  SSC Guru

  Points: 63573

  More actions

  December 27, 2017 at 6:33 pm

  #1973655

  Remove the s.StateDuration from the GROUP BY. You're SUMMING that... Your query should look like this:
  SELECT s.AgentName
  , s.ReasonCode
  , Sum(s.StateDuration) AS 'StateDuration'
FROM (
  SELECT [Agent Name] AS 'AgentName'
   , [Reason Code] AS 'ReasonCode'
   , Cast(Datepart(Second, [Duration]) AS INT) + Cast(60 * Datepart(Minute, [Duration]) AS INT) + Cast(3600 * Datepart(Hour, [Duration]) AS INT) AS 'StateDuration'
  FROM State_Details
  GROUP BY [Agent Name]
   , [Reason Code]
  ) s
GROUP BY s.AgentName
  , s.ReasonCode

• caldrumr1234

  Mr or Mrs. 500

  Points: 562

  More actions

  December 28, 2017 at 6:58 am

  #1973696

  So simple.  I was so close. 
  Thanks so much! I really appreciate it.

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  December 28, 2017 at 7:33 am

  #1973700

  There's no reason to have a subquery there.

  SELECT [Agent Name] AS AgentName
  , [Reason Code] AS ReasonCode
  , SUM(Cast(Datepart(Second, [Duration]) AS INT) + Cast(60 * Datepart(Minute, [Duration]) AS INT) + Cast(3600 * Datepart(Hour, [Duration]) AS INT)) AS StateDuration
FROM State_Details
GROUP BY [Agent Name]
  , [Reason Code]


  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass