1

I am trying to get some graph about different entries in a column in Grafana, but Grafana tries to recognize the antry as a column instead of a column value.

I have the following setup:

A PostgreSQL Database (v11).

A table called People. This table contains 3 columns: id, name, age.

I want to get the average age for each name contained within the name column.

I have defined a query variable called firstname in Grafana, which is returned by the query:

SELECT DISTINCT(name) from People;

It shows then a dropdown list containing all the unique names.

To get the average age for each name, i write the following query in Grafana:

SELECT AVG(age), pit_date FROM People WHERE name = $firstname GROUP BY pit_date LIMIT 15;

But i get the error: db query error: pq: column "selena" does not exist

My understanding is that Grafana is trying to locate teh column "selena" instead of "selena" within column "name".

Do you have any idea where i am wrong and how to solve this problem? I would appreciate it very much.

albdev
  • 43
  • 5
  • 1
    What's the purpose of the braces when using `DISTINCT`? I think is is misleading because it makes the impression `DISTINCT` is a function. – Jonas Metzler Aug 30 '23 at 11:08
  • You are right. The solution would be to declare the query as ... WHERE name in ( $firstname ).... . One also needs to check in the Variable Settings `Include All Option` for it to really work. – albdev Sep 01 '23 at 08:29

3 Answers3

2

Problem is caused by lack of quotes around you variable substitution.

After Grafana substituted firstname variable where clause of you query looks like this: name = selena. From SQL perspective selena is expected to be column name. To make it a string you need to wrap substitution with single quotes:

SELECT AVG(age), pit_date 
FROM People 
WHERE name = '$firstname' 
GROUP BY pit_date 
LIMIT 15
markalex
  • 8,623
  • 2
  • 7
  • 32
1

I think that in Grafana, you need to use '' to indicate template variables within your query. Try this sintax

SELECT AVG(age), pit_date FROM People WHERE name = '$firstname' GROUP BY pit_date LIMIT 15

I hope it helps bro

KamiSama
  • 21
  • 3
0

What worked for me is the following query:

SELECT AVG(age), pit_date FROM People WHERE name in ( $firstname ) GROUP BY pit_date LIMIT 15;

It is also important to check the Option Include All in the variable settings, for the changes to appear.

albdev
  • 43
  • 5