Need to run KACE Helpdesk report with open tickets by queues but also need to display queues having zero tickets.

Example:
Queue1       10
Queue2        20
Queue3        0
Queue4        10

I have SQL displaying queues with tickets open but cant make the rest of the queues to display zero's (0's).

  SELECT
HD_QUEUE.NAME AS department,
IFNULL(COUNT(HD_TICKET.ID),0) AS count
FROM HD_TICKET
LEFT JOIN HD_STATUS on HD_STATUS_ID = HD_STATUS.ID 
JOIN HD_QUEUE on HD_QUEUE.ID = HD_TICKET.HD_QUEUE_ID
WHERE (HD_TICKET.HD_QUEUE_ID in (58,63,60,47,33,67,59,62,64)) AND (HD_STATUS.STATE = 'stalled' OR HD_STATUS.STATE = 'opened')
GROUP BY department

Thanks in advance for the help.
Answer Summary:
grayematter answer: SELECT HD_QUEUE.NAME AS department, IFNULL(tc.ticket_count, 0) AS count FROM HD_QUEUE LEFT JOIN (SELECT q.id, COUNT(t.ID) AS ticket_count FROM HD_TICKET t LEFT JOIN HD_STATUS ON HD_STATUS_ID = HD_STATUS.ID LEFT JOIN HD_QUEUE q ON q.ID = t.HD_QUEUE_ID WHERE (HD_STATUS.STATE = 'stalled' OR HD_STATUS.STATE = 'opened') GROUP BY t.HD_QUEUE_ID) tc ON tc.id = HD_QUEUE.ID WHERE (HD_QUEUE.ID in (58,63,60,47,33,67,59,62,64)) ORDER BY department
Cancel
0 Comments   [ + ] Show Comments

Comments

  • This content is currently hidden from public view.
    Reason: Removed by member request
    For more information, visit our FAQ's.
Please log in to comment

Answers

1
Give this a try:

SELECT 
    HD_QUEUE.NAME AS department,
    IFFNULL(tc.ticket_count, 0) AS count
FROM
    HD_QUEUE
        LEFT JOIN
    (SELECT 
        q.id, COUNT(t.ID) AS ticket_count
    FROM
        HD_TICKET t
    LEFT JOIN HD_STATUS ON HD_STATUS_ID = HD_STATUS.ID
    LEFT JOIN HD_QUEUE q ON q.ID = t.HD_QUEUE_ID
    WHERE
        (HD_STATUS.STATE = 'stalled'
            OR HD_STATUS.STATE = 'opened')
    GROUP BY t.HD_QUEUE_ID) tc ON tc.id = HD_QUEUE.ID
WHERE (HD_QUEUE.ID in (58,63,60,47,33,67,59,62,64))
ORDER BY department

Answered 09/10/2014 by: grayematter
Fourth Degree Black Belt

  • Just removed an extra F in IFFNULL and Worked Great!! Thanks grayematter.
Please log in to comment
Answer this question or Comment on this question for clarity