CODEWITHSUNDEEP
mysqlsqlsum
Geoff_S
Geoff_S

Reputation: 5107

Sum values without aliases correctly

I have a query that is returning everything correctly except for some slight inconsistencies on one of my total columns (sum) and I can't figure out why it's happening or a better way to approach it.

Here's the section of the query in question:

SELECT 
 c.extension 
,RESPONSIBLEUSEREXTENSIONID 
 , sum(Duration)  -- seconds
, round(sum(Duration) / 60,2) -- minutes
, sum(if(LEGTYPE1 = 1,1,0))  -- outbound
, sum(if(LEGTYPE1 = 2,1,0) and ANSWERED = 1) -- inbound
, sum(if(Answered = 1,0,1))  -- missed
, count(if(LEGTYPE1 = 1,1,0)) + count(if(LEGTYPE1 = 2,1,0))   -- as total_calls 
, now()
, 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()
AND c.extension IN (7276,7314,7295,7306,7357,7200,7218,7247,7331,7255,7330,7000,7215,7240,7358,7312)
group by DATE(b.ts),c.extension;

And here are the first few results:

enter image description here

The total calls column should add inbound, outbound and missed calls. You can see that most of the time, the total is correct, but especially in the first and third row, it is off. If I give the formulas AS aliases and try to sum the aliases, it says the column doesn't exist.

Is there something I"m missing that's causing the inconsistency and more importantly, is there a more direct way I can sum those 3 columns/values?

Upvotes: 0

Views: 41

Answers (2)

Guy Segev
Guy Segev

Reputation: 1835

First you need to use SUM instead of COUNT on total_calls. Second, if you want to use aliases you need to wrap it all in another SELECT like this: 

SELECT 
*,
(t.inbound + t.outbound) total_calls
FROM
(SELECT
    c.extension,
    RESPONSIBLEUSEREXTENSIONID, 
    sum(Duration) seconds, 
    round(sum(Duration) / 60,2) minutes,
    sum(if(LEGTYPE1 = 1,1,0)) outbound, 
    sum(if(LEGTYPE1 = 2,1,0) and ANSWERED = 1) inbound,
    sum(if(Answered = 1,0,1)) missed,
    now(),
    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()
    AND c.extension IN (7276,7314,7295,7306,7357,7200,7218,7247,7331,7255,7330,7000,7215,7240,7358,7312)
    GROUP BY DATE(b.ts),c.extension) t;

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521289

You defined the total calls as the sum of inbound, outbound, and missed calls, yet in your query you only included 2 of these 3 terms:

count(if(LEGTYPE1 = 1,1,0)) + count(if(LEGTYPE1 = 2,1,0))

In addition, using COUNT there is probably not what you intended. This would just count the number of records, assuming all values are not NULL. Instead, just sum the three types of call sums using SUM:

Use SUM, not COUNT, when computing the total calls:

SUM(IF(LEGTYPE1 = 1, 1, 0)) +                   -- outbound
SUM(IF(LEGTYPE1 = 2, 1, 0) AND ANSWERED = 1) +  -- inbound
SUM(IF(Answered = 1, 0, 1)) AS total_calls      -- missed

Upvotes: 1

Related Questions