/build/static/layout/Breadcrumb_cap_w.png

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;


0 Comments   [ + ] Show comments

Answers (1)

Posted by: IgorAngelini 2 years ago
Second Degree Blue Belt
0

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

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ