# How to have multiple queries (i.e. graph lines) in one panel based on multi-valued variable settings **URL:** \ **Category:** PostgreSQL\ **Tags:** postgres, templating\ **Created:** [May 20, 2020, 3:15am UTC](https://community.grafana.com/t/how-to-have-multiple-queries-i-e-graph-lines-in-one-panel-based-on-multi-valued-variable-settings/30563 "2020-05-20T03:15:07Z")\ **Posts on this page:** 4\ **Page:** 1
**Author:** ![davehayes](https://avatars.discourse-cdn.com/v4/letter/d/f17d59/32.png) [@davehayes](https://community.grafana.com/u/davehayes)\ **Post date:** [May 20, 2020, 3:15am UTC](https://community.grafana.com/t/how-to-have-multiple-queries-i-e-graph-lines-in-one-panel-based-on-multi-valued-variable-settings/30563/1 "2020-05-20T03:15:08Z")
So I have a table like this: ``` time | host | metric -------|--------|---------- t0 | 1 | v0 t0 | 2 | v1 t0 | 3 | v2 t1 | 1 | v3 ... etc ... ``` In essence, the time is in the first column, the host is in the second column, and the metric for that {host,time} pair is in the 3rd. I have a variable that represents the host that I would like to make multi-valued, so you can display comparative graphs per host on the same panel. I’m using the postgresql data source and the single value host variable works just fine with the query builder. As soon as I make this multi-valued, the panel breaks and displays nothing. How do I set up grafana to handle multiple values for the host variable, such that it will display multiple host graphs on the same panel for comparison? Thanks in advance for any reply. ---
**Author:** ![cmukai](https://avatars.discourse-cdn.com/v4/letter/c/f17d59/32.png) [@cmukai](https://community.grafana.com/u/cmukai)\ **Post date:** [September 10, 2021, 7:07pm UTC](https://community.grafana.com/t/how-to-have-multiple-queries-i-e-graph-lines-in-one-panel-based-on-multi-valued-variable-settings/30563/2 "2021-09-10T19:07:02Z")
I think I have a solution. It involves returning subqueries from the Grafana variable. For the above example assume the host column is a foreign key into a table called host which looks like this: ```auto id | name ---|----- 1 | host1 2 | host2 ... etc ... ``` and the name of the example table is called `metric`. The Grafana variable would look like this: ```auto SELECT name AS __text, format('(SELECT t1.metric FROM metric t1 WHERE t1.time=t2.time AND t1.host=''%s'') AS "%s"', id, name) AS__value FROM host ``` The first %s has two pairs of single quotes around it, while the second has a single pair of double quotes (at least this is what I had to do the get the SQL syntax to work for me). Make this variable multi-value and optionally turn on the All option. Let’s call this variable metric. Now the query for the panel would look like this: ```auto SELECT t2.time AS "time", ${metric:raw} FROM metric t2 WHERE $__timeFilter("time") GROUP BY 1 ORDER BY 1 ``` Hope this helps. ---
**Author:** ![cmukai](https://avatars.discourse-cdn.com/v4/letter/c/f17d59/32.png) [@cmukai](https://community.grafana.com/u/cmukai)\ **Post date:** [September 10, 2021, 10:51pm UTC](https://community.grafana.com/t/how-to-have-multiple-queries-i-e-graph-lines-in-one-panel-based-on-multi-valued-variable-settings/30563/3 "2021-09-10T22:51:20Z")
Unfortunately, this runs extremely slow. Every data point requires a query that returns a single value. Does not scale well for long time samples and/or many variables. ---
**Author:** ![cmukai](https://avatars.discourse-cdn.com/v4/letter/c/f17d59/32.png) [@cmukai](https://community.grafana.com/u/cmukai)\ **Post date:** [September 14, 2021, 2:34am UTC](https://community.grafana.com/t/how-to-have-multiple-queries-i-e-graph-lines-in-one-panel-based-on-multi-valued-variable-settings/30563/4 "2021-09-14T02:34:00Z")
I have a solution that runs reasonably well and that addresses the scaling problem for the number queries and the number of points per history. To deal with the number of queries we construct a temporary table where each column contains the time history for a host. Each column requires a single query to populate it. The table would be constructed with the following subqueries: ```auto FROM (SELECT DISTINCT time FROM metric) t LEFT JOIN (SELECT time,metric FROM metric WHERE host=1) t1 ON t.time=t1.time LEFT JOIN (SELECT time,metric FROM metric WHERE host=2) t2 ON t.time=t2.time etc. ``` To address the time point scaling we use the Grafana `$ __interval` variable and `$__ timeGroupAlias` macro: ```auto SELECT $__timeGroupAlias(t.time, $__interval), avg(t1.metric) AS host1, avg(t2.metric) AS host2, etc. FROM WHERE $__timeFilter(t.time) GROUP BY 1 ORDER BY 1 ``` To allow the user to manage the dashboard we will use 3 variables, one to present a drop down with host names and the other two chained from the first to fill in the SELECT and FROM subqueries. The drop down would be created with a query variable called `hosts` with the following definition: ```auto SELECT name FROM host ``` To define the subqueries in the SELECT we create a variable called `columns` with the following: ```auto SELECT string_agg(format('avg(t%s.cost) AS "%s"', id, name), ',') FROM host WHERE name IN ($hosts) ``` Then the subqueries in the FROM clause would be from a variable called `tables` with the following definition: ```auto SELECT '(SELECT DISTINCT time FROM metric) t ' || string_agg(format('LEFT JOIN (SELECT time,metric FROM metric WHERE host=%s) t%1$s ON t.time=t%1$s.time', id), ' ') FROM host WHERE name IN ($hosts) ``` Finally to put it all together the query with variables looks like: ```auto SELECT $__timeGroupAlias(t.time, $__interval), ${columns} FROM ${tables} WHERE $__timeFilter(t.time) GROUP BY 1 ORDER BY 1 ```