Sort mySQL query in a Bar Chart?

[n0ne117]

• 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

Thank you very much in advance for any help!

[infofcc3]

hello @n0ne117 ,
you can follow the step to resolve your issue i hope it prove helpful..

Step: 1 create table and insert dummy data in sql

CREATE TABLE power_data (
  device TEXT,
  power_usage_kwh FLOAT,
  usage_date DATE
);

INSERT INTO power (device, power_usage_kwh, usage_date) VALUES
('Walking-Pad', 211.7, '2025-01-18'),
('Gefrierschrank', 98.1, '2025-02-12'),
('Fernseher', 75.6, '2023-03-10'),
('Kuehlschrank', 162.4, '2024-09-01'),
('Server-Rack', 142.3, '2023-08-25'),
('Steckdose-Bad', 67.9, '2023-11-04'),
('Schreibtisch-2', 103.2, '2025-03-22'),

Step: 2 Integrate SQL as datasoure in grafan

Step: 3 Write query for grafana

SELECT *
FROM (
    SELECT 
        usage_date AS time,
        device,
        power_usage_kwh,
        CASE 
            WHEN usage_date BETWEEN $__timeFrom() AND $__timeTo() THEN 1
            ELSE 0
        END AS in_time_range
    FROM electric_uses.power_data
) AS sub
WHERE in_time_range = 1
ORDER BY time ASC;

Step: 4: Impliment transformation

Final output: Based on 6 month

Based on 1 year :

[infofcc3]

Hello @n0ne117 ,

Just checking in—did this solution resolve your issue, or are you still experiencing the same problem?