MySQL根据状态和队列名对多个列进行计数

Question

我想对这样的表进行查询，以包括仅包含"Support"的队列中已完成和未完成的计数。到目前为止，我能够检索到"完成"。计数，但无法准确地包括"遗漏";计数。

TABLE NAME "chat_contacts"
Queue    | Status 
---------------------
Sales    | Completed
Billing  | Completed
Support  | Completed
Support  | Completed
Support  | Completed
Support1 | Completed
Support1 | Completed
Support2 | Completed
Support2 | Missed

我目前正在使用这个查询，它只返回支持队列和完成计数。

SELECT
`Queue` as `Queue`,
count(*) as `Completed`,
FROM `chat_contacts`
WHERE `Status` = 'Completed'
AND `Queue` LIKE 'Support%'
GROUP BY `Queue`;

这给了我以下预期的结果:

Queue    | Completed
---------------------
Support  | 3
Support1 | 2
Support2 | 1

但是，我还想包括未接联系人的计数。我尝试添加以下子查询，但它显示了每个队列相同的值，这显然是错误的

查询:

SELECT
`Queue` as `Queue`,
count(*) as `Completed`,
(
SELECT count(*)
FROM `chat_contacts`
WHERE `Queue` LIKE 'Support%'
AND `Status` = 'Missed'
) AS `Missed`
FROM `chat_contacts`
WHERE `Status` = 'Completed'
AND `Queue` LIKE 'Support%'
GROUP BY `Queue`;

结果:

Queue    | Completed | Missed
------------------------------
Support  | 3         | 1
Support1 | 2         | 1
Support2 | 1         | 1

预期结果:

Queue    | Completed | Missed
------------------------------
Support  | 3         | 0
Support1 | 2         | 0
Support2 | 1         | 1

希望帮助改进查询，以实现已完成和未接联系人。

Answer 1

正如我在评论中已经建议的那样，您可以在查询中使用SUM和CASE语句。

在我看来，完整的查询应该是这样的:

SELECT
Queue,
SUM(CASE WHEN Status='Missed' THEN 1 ELSE 0 END) AS Missed,
SUM(CASE WHEN Status='Completed' THEN 1 ELSE 0 END) AS Completed
FROM `chat_contacts`
WHERE `Queue` LIKE 'Support%'
GROUP BY `Queue`;