Group tickets by month

I’m using MySQL as a datasource, want to query my tickets from zammad database an display the sum of new tickets created each month. I tried and tried, but all i got was this so far

with this query:

created_at AS ‘time’,
FROM tickets
__timeFilter(created_at) GROUP BY MONTH(created_at) ORDER BY __timeGroup(created_at,$__interval)

I tried to the fillvalue “previous”, which made the graph look a bit better but broke my total as it adds the filled points to my total. What i’m really trying to achieve is something like in this picture i found in another post but only by month:

But i’m really stuck and using Series mode as suggested in the other post doesn’t get me any further as i’m just starting with grafana. Could somebody have a heart and explain to me how i can achieve this?


1 Like

Got it working setting X-Axis Mode to Series, Format as Time series and modifying my sql query.

created_at AS ‘time’,
MONTHNAME(created_at) AS ‘metric’,
FROM tickets
GROUP BY MONTH(created_at)
ORDER BY created_at


I seem to still have a problem with that. As per below - query ran in MySQL returns expected results, but when used in Grafana (with additional $__timeFilter(created_at) ), Grafana says ‘no results’ (but doesn’t report issues with the query itself). Any ideas?

-> created_at AS time, MONTHNAME(created_at) AS metric, count(id)
-> FROM tickets
-> GROUP BY MONTH(created_at)
-> ORDER BY created_at
-> ;
| time | metric | count(id) |
| 2019-11-20 14:04:56 | November | 3 |
| 2019-12-10 16:32:39 | December | 1 |
| 2020-01-20 14:29:58 | January | 1 |
| 2020-02-27 12:32:30 | February | 7 |
| 2020-03-20 10:23:25 | March | 5 |
5 rows in set (0.000 sec)

You are my Savior. Perfectly Works.