CODEWITHSUNDEEP
sqlsql-serverfunctionsumaggregate-functions
Ibrahiem Rafiq
Ibrahiem Rafiq

Reputation: 73

SUM within SQL Query - Left Outer Join

Here is the Scenario:

Tables:

**CallMain:**

Call_ID = PK (INT)
Abandoned (INT) Either 1 or NULL


**CallHold:**

Hold_ID = PK (INT)

Call_ID = FK (INT)

StartTime (INT)

EndTime (INT)

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.

Now, to return this information in a query to show calls and their total hold times, I believe the SQL to be as follows:

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

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.

My problem is that where a call has multiple hold records it will appear twice in the result i.e:

Call_ID, HoldPeriod

212, 254

213, 154

214, 158

214, 25

214, 10

As can be seen above, Call 214 has multiple hold records totalling 193. How can I add this up in the query so it will show just the total hold period for that call instead of repeating the call record multiple times in the result?

Solution Being

SELECT CallMain.Call_ID, CallMain.Abandoned,
 sum(ISNULL((CallHold.EndTime - CallHold.StartTime),0)) AS HoldPeriodSeconds FROM CTIStatCall LEFT OUTER JOIN CallHold ON CallMain.Call_ID = CallHold.Call_ID group by CallMain.Call_ID, CallMain.Abandoned

@AshReva - Here is the 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
order by CallMain.Call_ID

Upvotes: 0

Views: 6245

Answers (3)

DevelopmentIsMyPassion
DevelopmentIsMyPassion

Reputation: 3591

Just use sum and group by clause. See if below works.

SELECT 
    CallMain.Call_ID, 
    CallMain.Abandoned,  
    sum(ISNULL((CallHold.EndTime - CallHold.StartTime),0)) AS HoldPeriodSeconds 
FROM CTIStatCall 
LEFT OUTER JOIN CallHold 
   ON CallMain.Call_ID = CallHold.Call_ID
group by CallMain.Call_ID, CallMain.Abandoned

Upvotes: 1

amit_g
amit_g

Reputation: 31270

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

Upvotes: 0

Dan Bracuk
Dan Bracuk

Reputation: 20804

Use an sql sum() function and group by clause.

SELECT CallMain.Call_ID, CallMain.Abandoned,  
sum(ISNULL((CallHold.EndTime - CallHold.StartTime),0)) AS HoldPeriodSeconds 
FROM CTIStatCall 
LEFT OUTER JOIN CallHold ON CallMain.Call_ID = CallHold.Call_ID
group by CallMain.Call_ID, CallMain.Abandoned

Upvotes: 0

Related Questions