时间戳

首页 > 解决方案 > 从另一个表计算所有

mysql - 从另一个表计算所有

问题描述

我有一张这样的桌子：

一个。tbl_cdr
tbl_cdr

湾。tbl_wo_notification_contacts
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);

问题是我想添加一个名为“Total Duration”的新列，它根据查询条件从 tbl_cdr 计算所有内容。

标签： mysql

解决方案

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

推荐阅读