How to sum the max value of every day?

Hi,

I want to display my heater activity in grafana but have problems with the correct query. I want to select a date or a time range in grafana and he should show me sum of that range.

But I have no Idea how to sum up the values in a query. I hope someone can help :slight_smile:

Firstly, you appear to have asked a completely different question in your
posting from what it says in the subject - one asks about the last value of
each day, the other asks about the sum over a time range.

Secondly, please tell us which version of Grafana you are using and which
back-end data store you are using, because this makes a difference to the
queries you can perform.

Regards,

Antony.

Iam using Grafana 7.06 and InfluxDB and I just found a similiar question here, but its for mysql and not InfluxDB.

And yes, my subject is a little confusing, sorry :slight_smile:

I see you’ve changed the subject to “how to sum the max value of every day?”

That’s rather different from either of what you said previously “how to select
the last value of each day” or “select a date or a time range in grafana and
he should show me sum of that range” :slight_smile:

Does this now mean that you want to take the maximum value each day over a
number of days, and add those values together?

Firstly I cannot think of a way to do that in Influx, since it doesn’t (to the
best of my knowledge) have any concept of “a day”, but secondly I wonder
whather I’ve understood your requirement correctly because I can’t think why I
would want to take a single maximum value of “heater activity” over a number
of days and then add them together. What would the result mean?

Regards,

Antony.

Yes, the maximum value each day over a number of days. I wasn’t sure if thats more a Influx or a Grafana question. This way I can see how long the heater was running for the range I set in Grafana. Just like the link above, just with InfluxDB instead of mysql. :slight_smile:

Grafana has almost no query language of its own - it supplies values such as
__from __to and $__timeFilter but the underlying capabilities of the queries
you can run are entirely determined by the back-end data store (in your case,
InfluxDB).

This is because Grafana creates queries, but it then runs those queries on the
data store, so you can only do what the data store language supports.

The MySQL query you point to uses the date() function in MySQL to identify one
day as distinct from the next, and I do not believe this function exists in
Influx’ query language.

If you can use Influx2 you might find that FluxQL supports what you want to do,
but I’m not familiar with that so I cannot say for certain - hopefully someone
else here with experience of FluxQL can step in and confirm.

Regards,

Antony.

I just found this:

SELECT SUM("max") FROM (
 SELECT MAX("value") 
 FROM "measurement" 
 WHERE time >= now() - 7d 
 GROUP BY time(1d)
)

I replaced time >= now() - 7d with $timeFilter and now I can also use the time range in Grafana.
I think thats the solution, the displayed stat is correct. Sadly the graph does not working anymore with that but I get the result I want. Thanks for your help :slight_smile:

I just found this:

SELECT SUM("max") FROM (
 SELECT MAX("value")
 FROM "measurement"
 WHERE time >= now() - 7d
 GROUP BY time(1d)
)

Oh, that group by time(1d) is a neat solution.

I replaced time >= now() - 7d with $timeFilter and now I can also use the
time range in Grafana. I think thats the solution, the displayed stat is
correct.

Excellent.

Sadly the graph does not working anymore with that but I get the result I
want.

Try using the Query Inspector to see what values are coming back from the
query, for Grafana to graph.

One suggestion based on similar things I’ve done in the past is to add another
“group by time(1d)” at the very end of your query (after the final right
bracket).

That may well tell Grafana that you want one value of sum() per day, and then
it might show a graph.

Good luck :slight_smile:

Antony.

The Query Inspector only gives me one value, that explains why the graph is not working anymore.^^

With another“group by time(1d)" the Inspector shows all relevant values, the graph is working but the stat is wrong again. He does not seem to sum the values up and only showing the last one.

Well, they’re two separate queries (the stat and the graph) aren’t they?

So, add the second Group By for the graph, and leave it out for the stat…

Antony.

Yes, that should work. :smile:

Thanks alot for your support :slight_smile:

This topic was automatically closed 60 minutes after the last reply. New replies are no longer allowed.