Sujan Shrestha Sujan Shrestha - 3 months ago 7
SQL Question

Query- Find no. of closed tickets from no. of tickets created

I have a query that finds number of ticket created by an agent per queue(department).

SELECT queue.name AS 'Queue', COUNT(ticket.id) AS '# of tickets'
FROM ticket
INNER JOIN
queue ON ticket.queue_id = queue.id
WHERE
ticket.create_by != 1
AND
DATE(ticket.create_time) >= DATE_SUB(CURDATE(),INTERVAL 1 day)
AND
ticket.queue_id IN ( SELECT id FROM queue WHERE valid_id=1 ORDER BY name)
GROUP BY queue.name;


What i want is to get how many tickets are closed from the tickets created. here is the structure of the result of above query:

Queue || # of tickets


Billing 12
Enterprise Support 7
Fiber Support 47
L2-Support 4
Retail Support 107
Sales 4


I am not so good in subqueries. thats why i am struggling in this query.
the following query shows the number of tickets that are in closed state

SELECT queue.name AS 'Queue', COUNT(ticket.id) AS '# of closed tickets'
FROM ticket
INNER JOIN
queue ON ticket.queue_id = queue.id
WHERE
ticket.create_by != 1
AND
ticket.ticket_state_id = '2'
AND
DATE(ticket.create_time) >= DATE_SUB(CURDATE(),INTERVAL 1 day)
AND
ticket.queue_id IN ( SELECT id FROM queue WHERE valid_id=1 ORDER BY name)
GROUP BY queue.name;


Ultimately i want to display no of tickets created by the queues and how many tickets are in closed state like this:

Queue || # of tickets || # of closed tickets



Billing 12 2
Enterprise Support 7 0
Fiber Support 47 22
L2-Support 4 1
Retail Support 107 60
Sales 4 1

Answer

When you need to aggregate both the set and its subset do it with CASE

SELECT queue.name AS 'Queue', COUNT(ticket.id) AS '# of tickets'
  , SUM(CASE WHEN ticket.ticket_state_id = '2' THEN 1 ELSE 0 END) AS '# of closed tickets'
FROM ticket
    INNER JOIN
        queue ON ticket.queue_id = queue.id
WHERE
    ticket.create_by != 1
AND
    DATE(ticket.create_time) >= DATE_SUB(CURDATE(),INTERVAL 1 day)
AND
    ticket.queue_id IN ( SELECT id FROM queue WHERE valid_id=1 ORDER BY name)
GROUP BY queue.name;
Comments