mysql - 从另一张 table 算起

我有一张这样的桌子：

一种。 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中进行计数。

最佳答案

将tbl_cdr按ContactsID分组以获得计数。

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