Multiple series with one query in Grafana using PostgresQL as datasource

Question

I have data in a Postgres table with roughly this form:

CREATE TABLE jobs
(
    id              BIGINT PRIMARY KEY,
    started_at      TIMESTAMPTZ,
    duration        NUMERIC,
    project_id      BIGINT
)

I also came up with a query that is kinda what I want:

SELECT
  $__timeGroupAlias(started_at,$__interval),
  avg(duration) AS "durations"
FROM jobs
WHERE
  project_id = 720
GROUP BY 1
ORDER BY 1

This query filters for one exact project_id. What I actually want is one line in the chart for each project that has an entry in the table, not for just one.

I fail to find a way to do that. I tried all different flavors of group by clauses I could think of, and also tried the examples I found online but none of them worked.

Answer 1

Try this Grafana PostgreSQL query:

SELECT
  $__timeGroupAlias(started_at, $__interval),
  project_id::text AS "metric",
  AVG(duration) AS "durations"
FROM jobs
WHERE $__timeFilter(started_at)
GROUP BY 1,2
ORDER BY 1