Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

osTicket v1.10 (stable) and Maintenance Release v1.9.15 are now available! Go get it now

Help with query for tickets per Topic

Hi,
Im trying to run a query to get the number of tickets per topic for all months of year.
Tables that i use are ost_ticket and ost_help_topic.
In osTicket, our help topics go up to 4 lvls. For example i have topics like this
1st topic > 2nd topic > 3rd topic > 4th topic
1st topic > 2nd topic > 3rd topic > 9th topic
5th topic > 6th topic
7th topic > 8th topic
7th topic > 9th topic
10th topic > 11th topic > 12th topic
13th topic

and tickets :
2 tickets at 1st topic > 2nd topic > 3rd topic > 4th topic
2 tickets at 1st topic > 2nd topic > 3rd topic > 9th topic
2 tickets at 10th topic > 11th topic > 12th topic


I run the following query :

SELECT c.topic as categ1, c2.topic as categ2, c3.topic as categ3 ,c4.topic as categ4,
        COUNT(CASE MONTH(ot.created) WHEN  1 THEN ot.ticket_id END) AS `1`,
            COUNT(CASE MONTH(ot.created) WHEN  2 THEN ot.ticket_id END) AS `2`,
            COUNT(CASE MONTH(ot.created) WHEN  3 THEN ot.ticket_id END) AS `3`,
            COUNT(CASE MONTH(ot.created) WHEN  4 THEN ot.ticket_id END) AS `4`,
            COUNT(CASE MONTH(ot.created) WHEN  5 THEN ot.ticket_id END) AS `5`,
            COUNT(CASE MONTH(ot.created) WHEN  6 THEN ot.ticket_id END) AS `6`,
            COUNT(CASE MONTH(ot.created) WHEN  7 THEN ot.ticket_id END) AS `7`,
            COUNT(CASE MONTH(ot.created) WHEN  8 THEN ot.ticket_id END) AS `8`,
            COUNT(CASE MONTH(ot.created) WHEN  9 THEN ot.ticket_id END) AS `9`,
            COUNT(CASE MONTH(ot.created) WHEN 10 THEN ot.ticket_id END) AS `10`,
            COUNT(CASE MONTH(ot.created) WHEN 11 THEN ot.ticket_id END) AS `11`,
            COUNT(CASE MONTH(ot.created) WHEN 12 THEN ot.ticket_id END) AS `12`,
        COUNT(ot.ticket_id) AS `totalscounts`
FROM ost_help_topic AS c
LEFT JOIN ost_help_topic AS c2
    ON c2.topic_pid = c.topic_id
LEFT JOIN ost_help_topic AS c3
    ON c3.topic_pid = c2.topic_id
LEFT JOIN ost_help_topic AS c4
    ON c4.topic_pid = c3.topic_id
JOIN ost_ticket AS ot
    ON c.topic_id = ot.topic_id
WHERE c.topic_pid = '0'
GROUP BY c.topic, c2.topic, c3.topic, c4.topic

Problem are :
a) that counts are wrong and
b) parent topic categories that have subcategories are not visible individually.
    Results :
   1st topic   | 2nd topic  | 3rd topic    |  4th topic  |   2
   1st topic   | 2nd topic  | 3rd topic    |  9th topic  |   2
   10th topic | 11th topic | 12th topic   |                |   2

   What i need :
   1st topic   | 2nd topic  | 3rd topic    |  4th topic  |   2
   1st topic   | 2nd topic  | 3rd topic    |  9th topic  |   2
   1st topic   | 2nd topic  | 3rd topic    |                |   4    <-basically totals from subtopics
   1st topic   | 2nd topic  |                  |                |   4    <-basically totals from subtopics
   1st topic   |                |                  |                |   4    <-basically totals from subtopics
   10th topic | 11th topic | 12th topic   |                |   2
   10th topic | 11th topic |                  |                |   2   <-basically totals from subtopics
   10th topic |                |                  |                |   2   <-basically totals from subtopics
Totals                                                                  6

If anyone have a solution please let me know.
P.S. Im using mariadb 10.1.x so "WITH" syntax not working.

Regards
George
Sign In or Register to comment.