Mysql, Sum with a condition

Question

Mysql, Sum with a condition

Navigation

#1 by (0 votes)

1

I am trying to add a time according to a condition but I can not find the way I would appreciate your collaboration.

At the moment I have the following query

SELECT 
    u.src,
    u.accountcode,
    COUNT(*) AS cantidad,
    SUM(u.disposition = 'ANSWERED') AS contestadas,
    SUM(u.disposition = 'NO ANSWER') AS No_contestadas,
    SEC_TO_TIME(SUM(ROUND(u.duration))) AS tiempomuerto,
    SEC_TO_TIME(SUM(ROUND(u.billsec))) AS totaltiempo
FROM
    asteriskdb.cdr u
WHERE
    u.start != ''
        AND CHARACTER_LENGTH(src) = 4
        AND start BETWEEN CAST('2018-09-13' AS DATE) AND CAST('2018-09-14' AS DATE)
        AND lastapp = 'dial'
        AND dcontext != 'predictivo'
GROUP BY u.src
ORDER BY totaltiempo DESC;

What I need is to add the value of the billsec field as dead time, but to condition it to add only when the disposition field is No answer.

Result

'4313', 'BAL', '105', '82', '23', NULL, '02:10:21'
'4818', 'CID', '164', '93', '71', '03:14:00', '02:09:37'
'4323', 'BL', '102', '72', '30', NULL, '01:51:27'
'4054', 'CO', '122', '74', '48', '02:38:18','01:51:00'
'4052', 'O', '265', '65', '199', '03:14:55', '01:49:48'
'4065', 'd', '150', '105', '45', NULL, '01:46:58'
'4315', 'BANL','85', '68', '17', NULL, '01:40:05'
'4108', 'RE', '15', '6', '9', '01:44:47', '01:37:58'
'4509', 'C', '112', '95', '17', NULL,'01:36:29'
'4513', 'C', '108', '62', '46', '02:09:59', '01:36:06'
'4816', 'C', '137', '63', '74', '02:25:00', '01:31:06'
'4317', 'BL', '76', '58', '18', NULL, '01:25:09'

mysql

asked by JHOAN SEBASTIAN VARGAS ACOSTA 13.09.2018 в 21:12

source

1 answer

Incorrect executable format Sequential Securities Program

score 0 · Accepted Answer

Try adding CASE to your field this way:

SELECT 
    u.src,
    u.accountcode,
    COUNT(*) AS cantidad,
    SUM(u.disposition = 'ANSWERED') AS contestadas,
    SUM(u.disposition = 'NO ANSWER') AS No_contestadas,
    (CASE WHEN u.disposition = 'NO ANSWER' 
        THEN SEC_TO_TIME(SUM(ROUND(u.duration))) END) AS tiempomuerto,
    SEC_TO_TIME(SUM(ROUND(u.billsec))) AS totaltiempo
FROM
    asteriskdb.cdr u
WHERE
    u.start != ''
        AND CHARACTER_LENGTH(src) = 4
        AND start BETWEEN CAST('2018-09-13' AS DATE) AND CAST('2018-09-14' AS DATE)
        AND lastapp = 'dial'
        AND dcontext != 'predictivo'
GROUP BY u.src
ORDER BY totaltiempo DESC;