Variables in MySQL

So If I have two tables in my MySQL DB as the pictures attached, is it possible to have a variable where I can query the two departments (or choose from the dropdown menu either 1 or 2)?
I tried to do it with MySQL with no success, and the documentation doesn’t provide examples for MySQL variables.
So in short, I want to create two variables as follow:
-Variable (drop down menu) and query to choose different tables, where in my graph I can combine and compare for example (value1_dep2.department2 and value1_dep1.department1)
-Variable (drop down menu) and query to choose the year, so I can compare the same table value for different years for example (value1_dep1 in 2019 vs value1_dep1 in 2020)

Picture2 Picture1

Maybe create a “view” within MySQL which is a union of the two tables, then within Grafana you just reference the view which is in effect a virtual single table, so it might be something like this:

create or replace view departments as
select 1 as department,
id as id,
date_dep1 as date_dep,
value1_dep1 as value1,
value2_dep1 as value2,
value2_dep1 as value2
from department1
select 2 as department,
id as id,
date_dep2 as date_dep,
value1_dep2 as value1,
value2_dep2 as value2,
value2_dep2 as value2
from department2

Then you can query you view just like a table with where clauses however you want.

If you don’t want a view you can do the same thing directly within your sql queries using subselect and a union like the example above.

1 Like

If you have a single view then you can define variables using that single view (e.g. a department variable as select distinct department from departments order by 1)

Do that for all your variables and if your query is using the view then the users can select whatever they want and have the results from the view.

1 Like

Thanks codlord,
Assume I have all the data in one table, so no views needed, how and what to write in the variable field and link it to the MySQL query in the panel? this is actually where I’m failing to make it, and if it happens there is an online example for that it would be great ( where I can see both SQL query and the variable query), just like other data sources but I couldn’t find any.
Thanks for your patience, I’m somehow new to grafana.

A variable of type query for example called Departments and your query would be:

select distinct department from departments order by 1

Probably enable the Multi-value option.
Probably enable the Include All option option.

Then in your actual query where clause you would have:

where ...
and department in ($Department)
and ...

1 Like