mysql - 统计另一个表中的所有内容

Question

我有一张这样的表:

a. tbl_cdr

b. tbl_wo_notification_contacts

我想要的是统计 tbl_wo_notification_contacts 和 tbl_cdr 中的所有内容。这是我的查询。

SELECT SUBSTRING(n.last_attempt,1,10) AS DATE, 
SUM(IF(n.STATUS=1, 1,0)) AS 'Success', 
SUM(IF(n.STATUS=2, 1,0)) AS 'Failed', 
COUNT(*) AS 'TotalCalls'
FROM tbl_wo_notification_contacts n
WHERE n.last_attempt >= '2019-05-01' AND n.last_attempt <= '2019-07-01'
GROUP BY SUBSTRING(n.last_attempt,1,10);

问题是我想添加名为“总持续时间”的新列，它根据查询条件对 tbl_cdr 中的所有内容进行计数。

Answer 1

按 ContactsID 对 tbl_cdr 进行分组以获取计数。

SELECT SUBSTRING(n.last_attempt,1,10) AS DATE, 
        SUM(IF(n.STATUS=1, 1,0)) AS 'Success', 
        SUM(IF(n.STATUS=2, 1,0)) AS 'Failed', 
        COUNT(*) AS 'TotalCalls',
        IFNULL(c.TotalDuration, 0) AS TotalDuration
FROM tbl_wo_notification_contacts n
LEFT JOIN (
    SELECT ContactID, COUNT(*) AS TotalDuration
    FROM tbl_cdr
    GROUP BY ContactID) AS c ON c.ContactID = n.id
WHERE n.last_attempt >= '2019-05-01' AND n.last_attempt <= '2019-07-01'
GROUP BY SUBSTRING(n.last_attempt,1,10);