I am trying to create a panel with a pie chart which contains an amount of elements that depends on the value given to a variable.
Currently, I have created a number of SQL queries in the pie chart that look as follows:
SELECT SUM(mvalue) AS "room $room1" FROM periodic_measurements WHERE (apartmentid = $apartment AND roomid = $room1 AND metric = 5 AND $__timeFilter(mtimestamp))
room1 is a query variable defined as follows:
SELECT DISTINCT roomid FROM periodic_measurements WHERE apartmentid = $apartment AND roomid != 0 ORDER BY roomid LIMIT 1 OFFSET 0;
I have created 10 of such variables for the first 10 rooms and each of the 10 queries in the Grafana pie chart panel uses one of such variables.
Each of such SQL queries in the Grafana panel provides a single numeric value.
However, each apartment has a different number of rooms so I am trying to find a more dynamic way.
Once the value of the variable apartment has been assigned, Grafana should show the energy of all the rooms of the apartment in the pie chart.
Is there any way Grafana could accept a SQL query containing a loop as input for a pie chart?
I was thinking something like this (tested in SQL CLI):
DO $$ DECLARE number_of_rooms INT; room_ids INT ; room_energy FLOAT(6); BEGIN SELECT count(DISTINCT roomid) INTO number_of_rooms FROM periodic_measurements WHERE apartmentid = 4 AND roomid != 0; SELECT ARRAY(SELECT DISTINCT roomid INTO room_ids FROM periodic_measurements WHERE apartmentid = 4 AND roomid != 0 ORDER BY roomid); FOR counter IN 1..number_of_rooms LOOP SELECT SUM(mvalue) INTO room_energy FROM periodic_measurements WHERE (apartmentid = 4 AND roomid = room_ids[counter] AND metric = 5 AND mtimestamp::TEXT LIKE '2023-05-15%'); RAISE NOTICE 'room %: %', room_ids[counter], room_energy; END LOOP; END $$;
where the values of
room_energy would be used in the pie chart and
room %, room_ids[counter] would go into the legend.
I am running Grafana version 9.5.1. on Linux Ubuntu 22.04.2.
Thanks a lot in advance!