Number of tickets by category report
I'm working on making a report to show the number of tickets by category and I have a query that works, but I'd like to show all categories even if the number of tickets for a category is 0
Here's what I have so far.
SELECT HD_QUEUE.NAME as 'Queue', HD_CATEGORY.NAME as 'Category', count(HD_TICKET.id) as 'Count'
FROM HD_QUEUE
INNER JOIN (HD_TICKET
INNER JOIN HD_CATEGORY ON HD_TICKET.HD_CATEGORY_ID = HD_CATEGORY.ID)
ON HD_QUEUE.ID = HD_TICKET.HD_QUEUE_ID
WHERE HD_TICKET.CREATED between (CURDATE() - INTERVAL 3 MONTH ) and CURDATE()
GROUP BY HD_QUEUE.NAME, HD_CATEGORY.NAME
ORDER BY count(HD_TICKET.id) DESC;
Answers (1)
The closest I could get was unioing a second query cross joining HD_QUEUE with HD_CATEGORY, but I can't filter the repeat rows, I don't know if it's going to help you in any way.
Try giving it a shot:
SELECT HD_QUEUE.NAME as 'Queue', HD_CATEGORY.NAME as 'Category' , count(HD_TICKET.id) as 'Count'
FROM HD_TICKET
LEFT JOIN HD_CATEGORY
ON HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID
LEFT JOIN HD_QUEUE
ON HD_QUEUE.ID = HD_TICKET.HD_QUEUE_ID
WHERE HD_TICKET.CREATED > NOW() - INTERVAL 3 MONTH
GROUP BY HD_QUEUE.NAME, HD_CATEGORY.NAME
UNION
SELECT HD_QUEUE.NAME as 'Queue', HD_CATEGORY.NAME as 'Category' , NULL
FROM HD_QUEUE
CROSS JOIN HD_CATEGORY
LEFT JOIN HD_TICKET
ON HD_QUEUE.ID = HD_TICKET.HD_QUEUE_ID
GROUP BY HD_QUEUE.NAME, HD_CATEGORY.NAME
