Tom N. - 1 month ago 9

MySQL Question

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))`

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`

.