I have a timeseries panel(Grafana8) where I want to show a sum of values overtime. The values are retrieved from an Influx database(version 1.8)
The problem is, the values should be persisted in influx one data point per hour, but it’s not always the case. Sometimes I see multiple datapoints in the databse, and this messes strongly with my query.
My query is like this:
SELECT sum(sum1) from (select sum(“number”) as sum1 FROM “measurement” WHERE <> AND $timeFilter) GROUP BY time($__interval) fill(none)
$__interval should be set at 1 hour, in theory, but since my data comes in at irregular intervals, the panel shows weird spikes. Same behaviour I get if I let Grafana to set the interval.
What if in your inner query instead of doing sum("number") you did mean("number"), and also added a GROUP BY time(1h) to that (inner) query? That way if multiple values are written in any given hour, their mean (instead of sum) will be taken so you’ll avoid the spike.
Hello,
Thanks four your answer.
The problem is I really have to sum up the values for this particular panel.
I tried with grouping at 10m, it’s a little better, but still does not work everytime.
Ok. I suppose there’s more to it than what you described initially.
Since you said
It stands to reason that you don’t want to actually sum values. Because if there’s just one value there’s no point in doing a sum. And if there is more than one value you shouldn’t sum because then you get the wrong result.
But it seems that I haven’t understood the situation. Happy to try to help if you can give a bit more detail in light of the above.
Also to clarify, my suggestion was to use a mean in your inner query, in order to weed out the duplicates. You’re still free to use a sum in the outer query.
