Tom N. Tom N. - 1 month ago 9
MySQL Question

Subtracting a value that is result of a sum

My query below works perfectly but I need to change one part of my calculation for a new metric.

Currently it's presenting the data like this (removing unnecessary columns for sake of space and time):

ID | Extension | Total Inbound | Total Outbound | Total Missed | Total Calls
----------------------------------------------------------------------------
1 1000 6 1 2 7


However, I want to change it to subtract missed from inbound. It needs to continue to add inbound and outbound for the total but I'm looking for this output:

ID | Extension | Total Inbound | Total Outbound | Total Missed | Total Calls
----------------------------------------------------------------------------
1 1000 4 1 2 7

Insert into test.ambitionLog30Days(Extension, ExtID, Total_Talk_Time_seconds,
Total_Talk_Time_minutes,Total_Outbound, Total_Inbound,
Missed_Calls, Total_Calls, Time_of_report,Date_of_report )
SELECT
c.extension
,RESPONSIBLEUSEREXTENSIONID
, sum(Duration)
, round(sum(Duration) / 60,2)
, sum(if(LEGTYPE1 = 1,1,0)) -- Total inbound calls
, sum(if(LEGTYPE1 = 2,1,0))
, sum(if(Answered = 1,0,1)) -- Total Missed calls
, sum(if(LEGTYPE1 = 1,1,0)) + sum(if(LEGTYPE1 = 2,1,0)) as total_calls
, b.ts
, b.ts
FROM cdrdb.session a
INNER JOIN cdrdb.callsummary b
ON a.NOTABLECALLID = b.NOTABLECALLID
INNER join cdrdb.mxuser c
ON a.RESPONSIBLEUSEREXTENSIONID = c.EXTENSIONID
WHERE b.ts BETWEEN curdate() - interval 30 day and curdate()
-- WHERE b.ts >= '20170723' and b.ts < '20170823'
AND c.extension IN (7276,7314,7295,7306,7357,7200,7218,7247,7331,7255,7330,7000,7215,7240,7358,7312)
group by c.extension,b.ts
ON duplicate key update Total_Talk_Time_seconds =values(Total_Talk_Time_seconds),
Total_Talk_Time_minutes =values(Total_Talk_Time_minutes),
Total_Outbound = values(Total_Outbound),
Total_Inbound = values(Total_Inbound),
Missed_calls = values(Missed_calls),
Total_Calls = values(Total_Calls),
Time_of_report = values(Time_of_report);


I tried subtracting
- sum(if(Answered = 1,0,1))
on the total inbound line but it just returned the values in my outbound columns as negative numbers so I think I just have some syntax wrong.

Answer Source

Just do the calculation:

SELECT c.extension, 
       RESPONSIBLEUSEREXTENSIONID ,
       sum(Duration),  
       round(sum(Duration) / 60, 2) ,
       sum(LEGTYPE1 = 1 and Answered = 1), -- Total inbound calls 
       sum(LEGTYPE1 = 2), 
       sum(case when Answered = 1 then 0 else 1 end)), -- Total Missed calls
       sum( LEGTYPE1 in (1, 2) ), as total_calls 
       b.ts

I also simplified the logic, by removing the if(). MySQL allows you to treat boolean expressions as numbers, with 1 for true and 0 for false. Note that missed can be expressed as sum( Answered <> 1 ), if Answered is never NULL.