Hi - i need a my sql query to find out how much tickets raised for each help topic for a particular department. If i put * as count for ticket id it gives me only one topic but not for all.

a month later

I did something similar to this last month.

If your table prefix is not ost_ replace it with what you custom used.
If you want a specific date range alter the range '2022-10-01' AND '2023-10-01'
You would have to alter this query to only pull for a specific department.
you can do that by adding a line AND ost_ticket.dept_id = '#' before the GROUP BY line.
note: change the # to the department ID that you want to see.
You can look at the ost_department table or in the ui to get the # associated with the department.

SELECT ost_help_topic.topic AS HelpTopic, COUNT(ost_ticket.topic_id) AS numberTickets
FROM ost_ticket
JOIN ost_help_topic ON ost_ticket.topic_id=ost_help_topic.topic_id
WHERE ost_ticket.ticket_pid is NULL
AND ost_ticket.created BETWEEN '2022-10-01' AND '2023-10-01'
GROUP BY ost_help_topic.topic
Write a Reply...