title: SQL SUM within Query and Group By Duplicates
body: <p>Here is the Scenario:</p>
  
  <p>Tables:
  <strong>CallMain:</strong></p>
  
  <p>Call_ID = PK (INT)
  Abandoned (INT) Either 1 or NULL</p>
  
  <p><strong>CallHold:</strong></p>
  
  <p>Hold_ID = PK (INT)</p>
  
  <p>Call_ID = FK (INT)</p>
  
  <p>StartTime (INT)</p>
  
  <p>EndTime (INT)</p>
  
  <p>Each call held in the CallMain table can have either 1, 0 or many hold records. Each time the call goes on hold an record is created referenced by the FK with the StartTime of the hold and the EndTime of the hold.</p>
  
  <p>Now, to return this information in a query to show calls and their total hold times, I believe the SQL to be as follows:</p>
  
  <pre><code>SELECT CallMain.Call_ID, CallMain.Abandoned,  
  ISNULL((CallHold.EndTime - CallHold.StartTime),0) AS HoldPeriodSeconds 
  FROM CTIStatCall 
  LEFT OUTER JOIN CallHold ON CallMain.Call_ID = CallHold.Call_ID
  </code></pre>
  
  <p>This query should return records for calls that have no hold records associated with them and should return them as NULLS. Calls with a single hold record appear correctly. The NULLS that are being returned by the LEFT OUTER JOIN are being managed with the ISNULL() function and being replaced with a zero as in effect that call has no hold time.</p>
  
  <p>My problem is that where a call has multiple hold records it will appear twice in the result i.e:
  Call_ID, HoldPeriod</p>
  
  <p>212, 254</p>
  
  <p>213, 154</p>
  
  <p>214, 158</p>
  
  <p>214, 25</p>
  
  <p>214, 10
  As can be seen above, Call 214 has multiple hold records totalling 193. I figured out that the query has to be:</p>
  
  <pre><code>SELECT
      CallMain.Call_ID,
      CallMain.Abandoned,  
      Sum(ISNULL((CallHold.EndTime - CallHold.StartTime), 0)) AS HoldPeriodSeconds 
  FROM
      CallMain
  LEFT OUTER JOIN
      CallHold
  ON
      CallMain.Call_ID = CallHold.Call_ID
  GROUP BY
      CallMain.Call_ID,
      CallMain.Abandoned
  </code></pre>
  
  <p>My question now is I have further columns in the MainCall table which are also going to be returned, CallStart, CallAns, CallEnd and these are used to calculate some other derived values. I am returning these and adding them to the group by clause but it goes back to displaying duplicate values again. How can I fix this? </p>
  
  <p>I already tried:</p>
  
  <blockquote>
  <pre><code>SELECT CallMain.Call_ID, dateadd(s,CallMain.StartTime, '1970-01-01') AS StartTime,
  dateadd(s,CallMain.AnsTime, '1970-01-01') AS AnsTime, 
  dateadd(s,CallMain.EndTime, '1970-01-01') AS EndTime,
  CallMain.Abandoned,
  (CallMain.AnsTime - CallMain.StartTime) AS RingPeriod,
  SUM(ISNULL((CallHold.EndTime - CallHold.StartTime),0)) AS HoldPeriod,
  (CallMain.EndTime - CallMain.AnsTime) - ISNULL((CallHold.EndTime - CallHold.StartTime),0) AS TalkPeriod
  FROM CallMain
  LEFT OUTER JOIN
  CallHold ON CallMain.Call_ID = CallHold.Call_ID
     GROUP BY CallMain.Call_ID, CallMain.Abandoned, CallMain.StartTime, CallMain.EndTime, CallMain.AnsTime, CallHold.EndTime, CallHold.StartTime 
  </code></pre>
    
    <p>order by CallMain.Call_ID</p>
  </blockquote>