SQL Query with variables in the SQL Statement

Grafana MySQL
1

Hello,

I’m new to Grafana, but already have some nice dashboards running.

  • Grafana is running in docker at a Synology
  • my database is mariasql, works fine
  • Database has a lot of tables, all have a timestamp ‘Time’ and a float value ‘Value’ as data.
  • my panel is a simple bar gauge.

One bar1 is filled this way :

SELECT SUM(Value) / 60000 AS ‘Bad’
FROM
openhab.Item98
WHERE
$__timeFilter(Time);

This works fine.

Another bar2 shall be filled this way :

SELECT SUM(Value)
FROM openhab.Item106
WHERE $__timeFilter(Time) INTO @a;
SELECT SUM(Value)
FROM openhab.Item107
WHERE $__timeFilter(Time) INTO @b;
SELECT @a+@b AS ‘FL’;

The query runs as SQL command line at my server or in a windows-client as well.

I expected that bar2 would show the sum of the two SQL variables a and b set by the two queries ?

What’s my mistake ??

The reason for this is that I have a database with some large values and a lot of tables with small values, and I want to summarize the small values to one bar.

Thx for any hint to get smarter :wink: .

2

Welcome

What happens if you ran these above 2 queries in sql server replacing the filter with proper sql query syntax?

3

Hello yosiaz,

thank you for your reply. I’m not a native english speaker, maybe my text was a little bit unclear.
As I wrote, the query (with replaced variable $__timeFilter) is working as expected in a sql client
(I use heidi sql). So sql syntax is correct. That’s why I’m a little bit helpless …

Daisy.

image

1 Like
4

You SQL is 3 separate statements. I think Grafana will only work with a 1 single SQL statement - Grafana passes a statement to your DB and your DB returns the results.

You may have to work out how to perform your SQL in a single statement, for example using subselects/UNION.