Hi there,
I’m currently trying to utilize grafana to create real time dashboards to graph customer service output metrics and am receiving an error when I try to create the graph.
My data is coming from a local SQL database, however the exact metrics that I’d like to plot require me to transform the data to calculate values such as the cumulative output sum over time. Below is the query that works on the command line, the translated query in grafana syntax for graphing, and the resulting error.
Here is the original working query MySQL, where I would like internal.Date_Time as the x-axis and cumulative_total as the y-axis:
SET @runningTotal = 0;
SELECT
internal.Date_Time,
(@runningTotal := @runningTotal + internal.Samples) AS cumulative_total
FROM
(SELECT
a.created_on AS Date_Time,
e.data_uploaded as Samples
FROM datastore_reaction AS r
JOIN datastore_dataset AS d on d.reaction_id = r.id
JOIN datastore_attachment AS a on a.dataset_id = d.id
JOIN datastore_experiment AS e on e.id = r.experiment_id
JOIN datastore_assay AS assay on assay.id = e.assay_id
WHERE r.sample_id IS NOT NULL AND e.ignore=0 AND assay.name IN (‘NGS’)
GROUP BY r.id HAVING min(a.created_on)) AS internal
ORDER BY Date_Time;
Here is my translation attempt to make a grafana graph:
SET @runningTotal = 0;
SELECT
UNIX_TIMESTAMP(internal.Date_Time) AS time_sec,
(@runningTotal := @runningTotal + internal.Samples) AS value
FROM
(SELECT a.created_on AS Date_Time, e.data_uploaded as Samples
FROM datastore_reaction AS r
JOIN datastore_dataset AS d on d.reaction_id = r.id
JOIN datastore_attachment AS a on a.dataset_id = d.id
JOIN datastore_experiment AS e on e.id = r.experiment_id
JOIN datastore_assay AS assay on assay.id = e.assay_id
WHERE r.sample_id IS NOT NULL AND e.ignore=0 AND assay.name IN (‘NGS’)
GROUP BY r.id HAVING min(a.created_on)) AS internal
WHERE $__timeFilter(time_sec)
ORDER BY time_sec ASC
Here is the resulting error:
Error 1064: You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near
‘SELECT
UNIX_TIMESTAMP(internal.Date_Time) AS time_sec,
(@runningTotal := @r’ at line 2
Has anyone seen this type of error before or tried to do these transformations within the grafana query?
Thanks!
-Teryn