Count/sum not working the way intended

Question

I'm hoping this is just an error in my formatting so i won't post more of my query than necessary.

I had a large query with multiple joins and several SUM arguments (the point of this query is to aggregate daily totals every time it gets ran).

This is my trouble line:

sum(case when (duration > 60 and LEGTYPE1 = 1) then 1 else 0 end)

I also tried this:

count(case when (duration > 60 and LEGTYPE1 = 1) then 1 else null end)

Here's the problem: The sum case returns either a 1 or 0, and the count line does return the count (13,14,16) but it's not the right number for my records.

Here's the whole query for context:

SELECT c.extension
      , sum(Duration) AS Total_Talk_Time_seconds
      , round(sum(Duration) / 60,2) AS Total_Talk_Time_minutes
      , sum(if(LEGTYPE1 = 1,1,0)) AS Total_Outbound
      , sum(if(LEGTYPE1 = 2,1,0) and ANSWERED = 1) AS Total_Inbound
      , sum(if(Answered = 1,0,1)) AS Total_Missed
      , SUM(IF(LEGTYPE1 = 1, 1, 0)) +                   -- outbound calls
        SUM(IF(LEGTYPE1 = 2, 1, 0) AND ANSWERED = 1) +  -- inbound calls
        SUM(IF(Answered = 1, 0, 1))  AS Total_Calls
      , NOW() AS Time_of_report
      , curdate() AS Date_of_report
  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 >= curdate()
  AND c.extension IN (7276,7314,7295,7306,7357,7200,7218,7247,7331,7255,7330,7000,7215,7240,7358,7312)
  group by c.extension

SO, when I do the count version of that line, the number matches the total number of calls, where it should only represent the outbound ones that last longer than 60 seconds. The data is correct and the fields are correct, I'm simply asking if my syntax is correct or if there's simply a better way to structure it.

I don't see anything wrong with your syntax or logic. Perhaps just: `SUM( duration > 60 AND LEGTYPE1 = 1)` may work though? — JNevill, Sep 08 '17 at 19:54
you should post a valid data sample and the expetced result .. the sintax seems right but the question is not clear to me — ScaisEdge, Sep 08 '17 at 19:56
whait, how is it? `The sum case returns either a 1 or 0, and the count line does return the count (13,14,16) ` what change? — Horaciux, Sep 08 '17 at 20:04
The `COUNT` and `SUM` versions should be equivalent. A common problem when doing aggregation with multiple joins is that some of the joins match multiple rows, so you end up multiplying the aggregate by that. — Barmar, Sep 08 '17 at 20:06
If that's the problem, see https://stackoverflow.com/questions/37978511/join-tables-with-sum-issue-in-mysql/37979049#37979049 — Barmar, Sep 08 '17 at 20:07
are you sure your Total_Calls is correct? It looks like it may count unanswered outbound calls twice. — Garr Godfrey, Sep 08 '17 at 20:09
@GarrGodfrey it counts the inbound and outbound, plus inbound that were 'missed' — Geoff_S, Sep 08 '17 at 20:15
Thank you guys, I got it after changing my statement for 'sum' — Geoff_S, Sep 08 '17 at 20:15

score -1 · Answer 1 · answered Sep 08 '17 at 22:49

-1

Not sure I follow your logic but:

sum(if(LEGTYPE1 = 2,1,0) and ANSWERED = 1) AS Total_Inbound

and

SUM(IF(LEGTYPE1 = 2, 1, 0) AND ANSWERED = 1) +  
    SUM(IF(Answered = 1, 0, 1))  AS Total_Calls

Seem wrong. Try:

sum(if(LEGTYPE1 = 2 and ANSWERED = 1, 1, 0) AS Total_Inbound

and

SUM(IF(LEGTYPE1 = 2 AND ANSWERED =1 , 1, 0) +  
    IF(Answered = 1, 0, 1)
)  AS Total_Calls

answered Sep 08 '17 at 22:49

Jacques Amar

1,803
1
10
12

This is actually not the segment in question, though. My question was regarding the credit for outbound calls which I did eventually solve. The portion that you're referring to has been operating for a while now and it is already working correctly. The LEGTYPE can be 1 or 2 in the other database, where answered is only 1 or 0. The ANSWERED variable only accounts for missed calls. – Geoff_S Sep 11 '17 at 16:42

Count/sum not working the way intended

1 Answers1