Table visualization in Grafana to make summary

Grafana Dashboards
1

Win11, Grafana v.10.2, MySQL datasource, MariaDB database.

I have many sensor data in different tables (Sensor1, Sensor2 …)
Fields are: ID/Timestamp/Countervalue
Sampling: in every 15 min.

I’d like to create a Table dashboard containing monthly consumptions.

Sensor1 Sensor2 Sensor100
01/23 223 3456 135
02/23 345 2345 246
03/23 123 5678 311

How could I make a simple query?

2

I’m not sure of what you’re asking…

But, since you are working with mysql, I assume you can use sub-queries, which should allow you to select data from different tables in one query.
assuming on month is 30 days

SELECT
    (SELECT Countervalue FROM sensor_table1),
    (SELECT Countervalue FROM sensor_table2)
FROM
    table
WHERE
    Timestamp BETWEEN 
        (SELECT MAX(Timestamp) FROM sensor_table1) - (60 * 60 * 24 * 30) 
        AND 
        (SELECT MAX(Timestamp) FROM sensor_table2)
3

Thank you for your quick comment.
I have different tables where timestamp and countervalues are different.
I already have a solution how to make monthly summary for one sensor.
With T AS (
Select
Time,
Year (Time) as Year,
Month(Time) as Month,
Counter-lag(Counter) over (order by Time) as Sensor1
FROM Database.sensor1
)
select concat(Month," / ",Year) as Month, sum(Sensor1) as S1 from T
where year=2023
group by Year, Month
order by Time

But if I have 100 data tables, how to make a summary in one dashboard table?

4

I’ve never done this, so I’m just speculating. But I guess the transformation option should allow you to do what you want. (Maybe with concatenate fields).
I’m not in a position to help you further haha…

5

Please post schema of tables.
What do each of the 100 tables contain?

6

Every sensor table has the same structure: ID/Time/Countervalue

ID Time Countervalue
200 2023.11.01 0:00 21900
201 2023.11.01 0:15 21910
…
300 2023.11.30 23:45 23900

Timestamps are different but record timing is 15 min in every table.
In the first step I have to calcultae the monthly consumption with the previous query

Sensor1
11 / 23 2000
12 / 23

And in the second step I have to put the sensor figures in one table dashboard

Sensor1 Sensor2 Sensor100
01/23 223 3456 135
02/23 345 2345 246
03/23 123 5678 311

The question is to do it with left join or union or how?
Left join can be right but just following grouping by month because in the original Time data in every table can be different.
ID can not be used.

7

Why did you opt for one table per sensor approach?

Union would be the way to go

1 Like
8

Because it was the original request for some reason. I know that it would be easyer to write every sensor data in one table but we can’t modify it.

I made a query with union of raw data tables but in this case I had to add 0 (zero) to rest of the sensors and it is not nice.

My new idea is to calculate (group by and sum) the monthly data for each sensors in subqueries and put together in the second step. And it could be left join in this case because we would use the Month instead of Time.
But I don’t know how to make it in sql.
Could you please help me?

9

I would say post your question to influxdb community forum as it is an influxql question rather than a grafana question

But someone else might chime in from this forum

1 Like