MySQL count multiple columns based on status and queue name-CodePudding

I would like to make a query on a table like this to include the count for both Completed and Missed for only the queues containing "Support". So far I am able to retrieve the "Completed" count but unable to accurately include the "Missed" count.

TABLE NAME "chat_contacts"

Queue    | Status 
---------------------
Sales    | Completed
Billing  | Completed
Support  | Completed
Support  | Completed
Support  | Completed
Support1 | Completed
Support1 | Completed
Support2 | Completed
Support2 | Missed

I am currently using this query which returns only the support queues and the completed count.

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

This gives me the following result which is expected.

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

However, I would also like to include the count for Missed contacts as well. I tried with adding the following sub-query but it shows the same value for each Queue which is obviously wrong

Query:

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

Result:

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

Expected Result:

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

Hoping for assistance to improve the query to achieve both Completed and Missed contacts.

CodePudding user response：

As I already suggested in the comment, you can use SUM and CASE statements in your query.

The full query, as I see it, would be something like this:

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