Query works in mysql but does not display correctly in grafana

Question

So I'm just building a small project that involves the use of MySQL and Grafana.

Here's my MySQL query and it's corresponding correct output:

select state, sum(total_cases) from PolDATA group by state order by sum(total_cases) desc limit 5;

Correct output:

I have enetered the same query in Grafana as well. Also, the format is Table and not Time Series. The output in Grafana is this: when I display as a gauge. Here I do not want the text to be sum(total_cases). I want it to display the name of the state. I tried altering it in Visualization->Field->Title but that changes all the labels to the same value. I even tried adding to the query select sum(total_cases) as "Sometext" but it changes all the labels to "Sometext" instead.

Help is appreciated. Thank you (:

Your question has nothing to do with MySql, so don't set irrelevant tags. — forpas, Apr 28 '20 at 14:59
i'm sorry but isn't the MySQL query a part of MySQL? because there's a possibility that my query might be incorrect for usage in grafana — Blazing Phoenix, Apr 28 '20 at 15:07

score 1 · Answer 1 · answered Apr 30 '20 at 05:29

First you need to configure your Grafana as the below image.
You need to write pivot table query as below.

select
sum(case when state = 'MAHARASHTRA'  then total_cases else 0 end) as "MAHARASHTRA",
sum(case when state = 'GUJARAT'  then total_cases else 0 end) as "GUJARAT",
sum(case when state = 'MADHYA PRADESH'  then total_cases else 0 end) as "MADHYA PRADESH",
sum(case when state = 'RAJASTHAN'  then total_cases else 0 end) as "RAJASTHAN",
sum(case when state = 'DELHI'  then total_cases else 0 end) as "DELHI"
from PolDATA
group by state

E.g.

Note: If your "state" column is dynamic, then you need to try dynamic pivoting.

E.g. https://stackoverflow.com/a/12599372/4249637

Query works in mysql but does not display correctly in grafana

1 Answers1