Sum of Max value of a day for a period of time

Grafana InfluxDB
1

Hello Everyone,

I will prefix this with I am a total noob. I have my weather station pumping data into the database and I am wanting to track rainfall for a period (i.e last 7 days or last 30 days). The rain data comes in as a aggregate of the day i.e:

14-08-2019 15:30 20.00
14-08-2019 16:00 20.10
14-08-2019 16:30 20.10

I know i need to get the max value of each day, then for a period of 7 days or 30 days (or however long i want to track) I need to add those days together. I have seen this post - Sum the max value of every day in grafana and Singlestat sum of max values for different tags in a measurement. However I am too much of noob to fully understand how to implement the solutions in this instance. I am hoping someone could help me out to achieve what I want.

Cheers!

2
  • Daily MAXes for last 7 days
SELECT MAX("value") 
   FROM "measurement" 
   WHERE time >= now() - 7d 
   GROUP BY time(1d)
  • SUM daily MAXes for last 7 days
SELECT SUM("max") FROM (
   SELECT MAX("value") 
     FROM "measurement" 
     WHERE time >= now() - 7d 
     GROUP BY time(1d)
)

Use https://docs.influxdata.com/influxdb/v1.7/ if my queries have syntax error or you need more details.

3

This is not working for med. Any suggestions ?

> SELECT SUM("max") FROM (SELECT MAX("value") FROM "Kr" WHERE "entity_id" = 'total_cost' AND time >= now() - 7d GROUP BY time(1d))

ERR: error parsing query: found (, expected identifier at line 1, char 24

EDIT: the SELECT MAX statement is working

Thomas

4

This is not working for med. Any suggestions ?

What is your back-end data store?

SELECT SUM(“max”) FROM (SELECT MAX(“value”) FROM “Kr” WHERE “entity_id” =
‘total_cost’ AND time >= now() - 7d GROUP BY time(1d))

ERR: error parsing query: found (, expected identifier at line 1, char 24

I have something almost identical which works fine for me using InfluxDB:

select sum(max) from (select max(CIP) from mqtt_consumer where(topic=‘Live’)
and server!=‘’ and $timeFilter group by server,time(60s)) group by time(60s)
fill(linear)

So, I suspect that syntax isn’t valid for the data store you’re using.

Antony.

5

i am trying from InfluxDB command line interface on the same server as the influxdb is running.

6

UPDATE: I had influxdb 1.1.1 and subqueries where introduced in 1.2. Working Ok after upgrade

7

Jangaraj, is there a way in influxdb to subtract the sum of max values for 1 week, vs the sum of the values from the previous week?
to find out how much its changed from 1 week to another?