Sort mySQL query in a Bar Chart?

Grafana Bar Chart Panel
1
  • What Grafana version and what operating system are you using?

Grafana v11.5.2 (598e0338d5) as a Docker Container on Alma Linux

  • What are you trying to achieve?

Have the output of a mySQL query sorted by single total power usage of the devices, shown as a Bar Chart.

  • How are you trying to achieve it?

I tried sorting inside the query, splitting into multiple queries, checking if a transformation will work and read if the legend can be used as sorting parameter - but I can’t seem to get it working.

  • What happened?

The data for each device is correctly displayed - but ordered as specified in the query.

  • What did you expect to happen?

As mentioned above - I really want the data sorted dynamically. I have the same query for yearly, monthly and daily visualization - manually sorting is not an option.

  • Can you copy/paste the configuration(s) that you are having problems with?

This is the query I currently have without trying to sort it per device.

SELECT 
    STR_TO_DATE(CONCAT(year, '-01-01'), '%Y-%m-%d') AS time, 
    SUM(CASE WHEN device = 'Server-Rack' THEN power_usage_kwh ELSE 0 END) AS `Server-Rack`,
    SUM(CASE WHEN device = 'Walking-Pad' THEN power_usage_kwh ELSE 0 END) AS `Walking-Pad`,
    SUM(CASE WHEN device = 'Waschmaschine' THEN power_usage_kwh ELSE 0 END) AS `Waschmaschine`,
    SUM(CASE WHEN device = 'Kuehlschrank' THEN power_usage_kwh ELSE 0 END) AS `Kuehlschrank`,
    SUM(CASE WHEN device = 'Schreibtisch-1' THEN power_usage_kwh ELSE 0 END) AS `Schreibtisch-Alex`,
    SUM(CASE WHEN device = 'Steckdose-Bad' THEN power_usage_kwh ELSE 0 END) AS `Steckdose-Bad`,
    SUM(CASE WHEN device = 'Fernseher' THEN power_usage_kwh ELSE 0 END) AS `Fernseher`,
    SUM(CASE WHEN device = 'Schreibtisch-2' THEN power_usage_kwh ELSE 0 END) AS `Schreibtisch-Andrea`,
    SUM(CASE WHEN device = 'Gefrierschrank' THEN power_usage_kwh ELSE 0 END) AS `Gefrierschrank`,
    SUM(CASE WHEN device = 'Amps' THEN power_usage_kwh ELSE 0 END) AS `Amps`
FROM power_usage_db.power_usage
GROUP BY year
ORDER BY year ASC;
  • Did you receive any errors in the Grafana UI or in related logs? If so, please tell us exactly what they were.

No.

  • Did you follow any online instructions? If so, what is the URL?

Did not find any promising instructions - even wasted half a day with ChatGPT :slight_smile:

Thank you very much in advance for any help!