Stat visualization with aggregated graph but non-aggregated last value

  • What Grafana version and what operating system are you using?
    v9.3.2
  • What are you trying to achieve?
    In stat visualisation I’m trying to display the last value as text but use aggregated data for the graph. Data in my postgres database is raw, non-aggregated. I’m using this query to obtain the data for the graph, for displayed Value I’m using Calculate > Last *. However since the query aggregates the rows to some interval ($__interval) even the last row is aggregated. Is it possible to somehow display the graph aggregated but keep the Value really the last, non-aggregated row?

SELECT $__timeGroup(logdate, ‘$__interval’, NULL) as time,
AVG(value) as “Value”
FROM table
WHERE logdate >= now()::date AND logdate <= now()
GROUP BY time
ORDER BY time ASC

  • How are you trying to achieve it?
    I tried getting the aggregated data and using UNION also select the last row explicitly as non-aggregated like this:

(
select $__timeGroup(logdate, ‘$__interval’, NULL) as time,
AVG(value) as “Value”
FROM table
WHERE logdate >= now()::date AND logdate <= now()
GROUP BY time
ORDER BY time ASC
) UNION (
SELECT
extract(epoch from logdate),
value
FROM table
WHERE logdate >= now()::date AND logdate <= now()
ORDER BY logdate DESC
LIMIT 1
)

  • What happened?
    It totally destroyed the graph.

  • What did you expect to happen?
    That the graph would display the aggregated values + last one as non-aggregated and the Value would display just the last non-aggregated value.

  • Can you copy/paste the configuration(s) that you are having problems with?

  • Did you receive any errors in the Grafana UI or in related logs? If so, please tell us exactly what they were.

No errors.

  • Did you follow any online instructions? If so, what is the URL?

I was able to solve the broken graph by explicitly sorting one more time. I thought that if I sort selectA and UNION it with selectB which only contains one row which is guaranteed to be newest then the overall result would be sorted, but no. Apparently it doesn’t work that way.

SELECT * FROM
(
select $__timeGroup(logdate, ‘$__interval’, NULL) as time,
AVG(value) as “Value”
FROM table
WHERE logdate >= now()::date AND logdate <= now()
GROUP BY time
ORDER BY time ASC
) UNION (
SELECT
extract(epoch from logdate),
value
FROM table
WHERE logdate >= now()::date AND logdate <= now()
ORDER BY logdate DESC
LIMIT 1
)) as x
ORDER BY x.time ASC;

seems to work.

Is there any other way - e.g. specify separate query for the graph in Stat visualisation and another separate query for the Value in stat visualisation?

Thanks.