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:

SELECT
created_at AS ‘time’,
count(*)
FROM tickets
WHERE
__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?

Greets
Sebbo

1 Like

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

SELECT
created_at AS ‘time’,
MONTHNAME(created_at) AS ‘metric’,
count(id)
FROM tickets
WHERE
$__timeFilter(created_at)
GROUP BY MONTH(created_at)
ORDER BY created_at

4 Likes

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?

SELECT
-> 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.