Grouping and sum

1

Hi,

I’ve got a table with 3 devices sending an always increasing counter (electrical watt produced). I would like to convert into watt/hour and then sum the value of the 3 devices to get the watt/hour produced.

Here is my current flux:

from(bucket: “pv”)
|> range(start: dashboardTime)
|> filter(fn: ® => r._field == “energy”)
|> window(every: 1h)
|> reduce(fn: (r, accumulator) => ({
index: accumulator.index + 1,
first: if accumulator.index == 0 then r._value
else accumulator.first,
last:r._value,
delta: accumulator.last-accumulator.first,

}), identity: {index: 0, first:0.0, last: 0.0, delta:0.0})
|> drop(columns: [“first”, “last”, “index”])
|> rename(columns: {delta: “_value”})
|> duplicate(column: “_stop”, as: “_time”)
|> window(every: inf)

The output is three tables with the watt produced each hours but i do not know how to merge into one table and execute the sum. I’ve tried with group which works but i cannot sum. (and the visualisation is only reporting the value of one device)

Thanks for your help

2

Hello @ced2flux,
Welcome! That’s exciting that you’re using reduce()!
I think this thread would be useful to you:

Also you might be able to do this without reduce?

Let me know if these resources help. Can you provide a little bit of annotated csv of your data before the transformation and your expected result for me to try and write the query?

Thank you!

3

Hello,

Thanks for the difference() function. I’ve rewritten my query but still blocked.

Here is now my query which does the same result as before. I’m not allowed to upload csv nor txt.

At the end, i get 3 tables. I’ve tried to regroup all the table in one unique table then resplit the table based on “_time”

|> group(columns: [“_field”], mode:“by”)
|> group(columns: [“_time”], mode:“by”)
|> sum()

with the hope to sum all rows for each table (based on _time) but it does not work.

Here is the csv before the last “group” function.

Blockquote ,result,table,_start,_stop,_time,_value,_field,_measurement,device
,0,2020-11-14T18:09:54.731857098Z,2020-11-16T18:09:54.731857098Z,2020-11-15T07:00:00Z,0,energy,2120198970,2120198970
,0,2020-11-14T18:09:54.731857098Z,2020-11-16T18:09:54.731857098Z,2020-11-15T08:00:00Z,149,energy,2120198970,2120198970
,0,2020-11-14T18:09:54.731857098Z,2020-11-16T18:09:54.731857098Z,2020-11-15T09:00:00Z,829,energy,2120198970,2120198970
,0,2020-11-14T18:09:54.731857098Z,2020-11-16T18:09:54.731857098Z,2020-11-15T10:00:00Z,1352,energy,2120198970,2120198970
,0,2020-11-14T18:09:54.731857098Z,2020-11-16T18:09:54.731857098Z,2020-11-15T11:00:00Z,865,energy,2120198970,2120198970
,0,2020-11-14T18:09:54.731857098Z,2020-11-16T18:09:54.731857098Z,2020-11-15T12:00:00Z,644,energy,2120198970,2120198970
,0,2020-11-14T18:09:54.731857098Z,2020-11-16T18:09:54.731857098Z,2020-11-15T13:00:00Z,558,energy,2120198970,2120198970
,0,2020-11-14T18:09:54.731857098Z,2020-11-16T18:09:54.731857098Z,2020-11-15T14:00:00Z,155,energy,2120198970,2120198970
,0,2020-11-14T18:09:54.731857098Z,2020-11-16T18:09:54.731857098Z,2020-11-15T15:00:00Z,3,energy,2120198970,2120198970
,0,2020-11-14T18:09:54.731857098Z,2020-11-16T18:09:54.731857098Z,2020-11-15T16:00:00Z,0,energy,2120198970,2120198970
,0,2020-11-14T18:09:54.731857098Z,2020-11-16T18:09:54.731857098Z,2020-11-15T17:00:00Z,0,energy,2120198970,2120198970
,0,2020-11-14T18:09:54.731857098Z,2020-11-16T18:09:54.731857098Z,2020-11-16T05:00:00Z,energy,2120198970,2120198970
,0,2020-11-14T18:09:54.731857098Z,2020-11-16T18:09:54.731857098Z,2020-11-16T07:00:00Z,0,energy,2120198970,2120198970
,0,2020-11-14T18:09:54.731857098Z,2020-11-16T18:09:54.731857098Z,2020-11-16T08:00:00Z,6,energy,2120198970,2120198970
,0,2020-11-14T18:09:54.731857098Z,2020-11-16T18:09:54.731857098Z,2020-11-16T09:00:00Z,79,energy,2120198970,2120198970
,0,2020-11-14T18:09:54.731857098Z,2020-11-16T18:09:54.731857098Z,2020-11-16T10:00:00Z,91,energy,2120198970,2120198970
,0,2020-11-14T18:09:54.731857098Z,2020-11-16T18:09:54.731857098Z,2020-11-16T11:00:00Z,118,energy,2120198970,2120198970
,0,2020-11-14T18:09:54.731857098Z,2020-11-16T18:09:54.731857098Z,2020-11-16T12:00:00Z,154,energy,2120198970,2120198970
,0,2020-11-14T18:09:54.731857098Z,2020-11-16T18:09:54.731857098Z,2020-11-16T13:00:00Z,134,energy,2120198970,2120198970
,0,2020-11-14T18:09:54.731857098Z,2020-11-16T18:09:54.731857098Z,2020-11-16T14:00:00Z,48,energy,2120198970,2120198970
,0,2020-11-14T18:09:54.731857098Z,2020-11-16T18:09:54.731857098Z,2020-11-16T15:00:00Z,64,energy,2120198970,2120198970
,0,2020-11-14T18:09:54.731857098Z,2020-11-16T18:09:54.731857098Z,2020-11-16T16:00:00Z,5,energy,2120198970,2120198970
,0,2020-11-14T18:09:54.731857098Z,2020-11-16T18:09:54.731857098Z,2020-11-16T17:00:00Z,0,energy,2120198970,2120198970
,1,2020-11-14T18:09:54.731857098Z,2020-11-16T18:09:54.731857098Z,2020-11-15T07:00:00Z,0,energy,2120199320,2120199320
,1,2020-11-14T18:09:54.731857098Z,2020-11-16T18:09:54.731857098Z,2020-11-15T08:00:00Z,228,energy,2120199320,2120199320
,1,2020-11-14T18:09:54.731857098Z,2020-11-16T18:09:54.731857098Z,2020-11-15T09:00:00Z,571,energy,2120199320,2120199320
,1,2020-11-14T18:09:54.731857098Z,2020-11-16T18:09:54.731857098Z,2020-11-15T10:00:00Z,978,energy,2120199320,2120199320
,1,2020-11-14T18:09:54.731857098Z,2020-11-16T18:09:54.731857098Z,2020-11-15T11:00:00Z,773,energy,2120199320,2120199320
,1,2020-11-14T18:09:54.731857098Z,2020-11-16T18:09:54.731857098Z,2020-11-15T12:00:00Z,628,energy,2120199320,2120199320
,1,2020-11-14T18:09:54.731857098Z,2020-11-16T18:09:54.731857098Z,2020-11-15T13:00:00Z,537,energy,2120199320,2120199320
,1,2020-11-14T18:09:54.731857098Z,2020-11-16T18:09:54.731857098Z,2020-11-15T14:00:00Z,158,energy,2120199320,2120199320
,1,2020-11-14T18:09:54.731857098Z,2020-11-16T18:09:54.731857098Z,2020-11-15T15:00:00Z,3,energy,2120199320,2120199320
,1,2020-11-14T18:09:54.731857098Z,2020-11-16T18:09:54.731857098Z,2020-11-15T16:00:00Z,0,energy,2120199320,2120199320
,1,2020-11-14T18:09:54.731857098Z,2020-11-16T18:09:54.731857098Z,2020-11-15T17:00:00Z,0,energy,2120199320,2120199320
,1,2020-11-14T18:09:54.731857098Z,2020-11-16T18:09:54.731857098Z,2020-11-16T05:00:00Z,energy,2120199320,2120199320
,1,2020-11-14T18:09:54.731857098Z,2020-11-16T18:09:54.731857098Z,2020-11-16T07:00:00Z,0,energy,2120199320,2120199320
,1,2020-11-14T18:09:54.731857098Z,2020-11-16T18:09:54.731857098Z,2020-11-16T08:00:00Z,7,energy,2120199320,2120199320
,1,2020-11-14T18:09:54.731857098Z,2020-11-16T18:09:54.731857098Z,2020-11-16T09:00:00Z,81,energy,2120199320,2120199320
,1,2020-11-14T18:09:54.731857098Z,2020-11-16T18:09:54.731857098Z,2020-11-16T10:00:00Z,92,energy,2120199320,2120199320
,1,2020-11-14T18:09:54.731857098Z,2020-11-16T18:09:54.731857098Z,2020-11-16T11:00:00Z,120,energy,2120199320,2120199320
,1,2020-11-14T18:09:54.731857098Z,2020-11-16T18:09:54.731857098Z,2020-11-16T12:00:00Z,157,energy,2120199320,2120199320
,1,2020-11-14T18:09:54.731857098Z,2020-11-16T18:09:54.731857098Z,2020-11-16T13:00:00Z,136,energy,2120199320,2120199320
,1,2020-11-14T18:09:54.731857098Z,2020-11-16T18:09:54.731857098Z,2020-11-16T14:00:00Z,50,energy,2120199320,2120199320
,1,2020-11-14T18:09:54.731857098Z,2020-11-16T18:09:54.731857098Z,2020-11-16T15:00:00Z,65,energy,2120199320,2120199320
,1,2020-11-14T18:09:54.731857098Z,2020-11-16T18:09:54.731857098Z,2020-11-16T16:00:00Z,5,energy,2120199320,2120199320
,1,2020-11-14T18:09:54.731857098Z,2020-11-16T18:09:54.731857098Z,2020-11-16T17:00:00Z,0,energy,2120199320,2120199320
,2,2020-11-14T18:09:54.731857098Z,2020-11-16T18:09:54.731857098Z,2020-11-15T07:00:00Z,0,energy,2120199323,2120199323
,2,2020-11-14T18:09:54.731857098Z,2020-11-16T18:09:54.731857098Z,2020-11-15T08:00:00Z,145,energy,2120199323,2120199323
,2,2020-11-14T18:09:54.731857098Z,2020-11-16T18:09:54.731857098Z,2020-11-15T09:00:00Z,514,energy,2120199323,2120199323
,2,2020-11-14T18:09:54.731857098Z,2020-11-16T18:09:54.731857098Z,2020-11-15T10:00:00Z,1135,energy,2120199323,2120199323
,2,2020-11-14T18:09:54.731857098Z,2020-11-16T18:09:54.731857098Z,2020-11-15T11:00:00Z,806,energy,2120199323,2120199323
,2,2020-11-14T18:09:54.731857098Z,2020-11-16T18:09:54.731857098Z,2020-11-15T12:00:00Z,632,energy,2120199323,2120199323
,2,2020-11-14T18:09:54.731857098Z,2020-11-16T18:09:54.731857098Z,2020-11-15T13:00:00Z,547,energy,2120199323,2120199323
,2,2020-11-14T18:09:54.731857098Z,2020-11-16T18:09:54.731857098Z,2020-11-15T14:00:00Z,153,energy,2120199323,2120199323
,2,2020-11-14T18:09:54.731857098Z,2020-11-16T18:09:54.731857098Z,2020-11-15T15:00:00Z,3,energy,2120199323,2120199323
,2,2020-11-14T18:09:54.731857098Z,2020-11-16T18:09:54.731857098Z,2020-11-15T16:00:00Z,0,energy,2120199323,2120199323
,2,2020-11-14T18:09:54.731857098Z,2020-11-16T18:09:54.731857098Z,2020-11-15T17:00:00Z,0,energy,2120199323,2120199323
,2,2020-11-14T18:09:54.731857098Z,2020-11-16T18:09:54.731857098Z,2020-11-16T05:00:00Z,energy,2120199323,2120199323
,2,2020-11-14T18:09:54.731857098Z,2020-11-16T18:09:54.731857098Z,2020-11-16T07:00:00Z,0,energy,2120199323,2120199323
,2,2020-11-14T18:09:54.731857098Z,2020-11-16T18:09:54.731857098Z,2020-11-16T08:00:00Z,8,energy,2120199323,2120199323
,2,2020-11-14T18:09:54.731857098Z,2020-11-16T18:09:54.731857098Z,2020-11-16T09:00:00Z,80,energy,2120199323,2120199323
,2,2020-11-14T18:09:54.731857098Z,2020-11-16T18:09:54.731857098Z,2020-11-16T10:00:00Z,91,energy,2120199323,2120199323
,2,2020-11-14T18:09:54.731857098Z,2020-11-16T18:09:54.731857098Z,2020-11-16T11:00:00Z,118,energy,2120199323,2120199323
,2,2020-11-14T18:09:54.731857098Z,2020-11-16T18:09:54.731857098Z,2020-11-16T12:00:00Z,153,energy,2120199323,2120199323
,2,2020-11-14T18:09:54.731857098Z,2020-11-16T18:09:54.731857098Z,2020-11-16T13:00:00Z,133,energy,2120199323,2120199323
,2,2020-11-14T18:09:54.731857098Z,2020-11-16T18:09:54.731857098Z,2020-11-16T14:00:00Z,50,energy,2120199323,2120199323
,2,2020-11-14T18:09:54.731857098Z,2020-11-16T18:09:54.731857098Z,2020-11-16T15:00:00Z,64,energy,2120199323,2120199323
,2,2020-11-14T18:09:54.731857098Z,2020-11-16T18:09:54.731857098Z,2020-11-16T16:00:00Z,5,energy,2120199323,2120199323
,2,2020-11-14T18:09:54.731857098Z,2020-11-16T18:09:54.731857098Z,2020-11-16T17:00:00Z,0,energy,2120199323,2120199323

4

Hi,

I’ve simplified the query and just added sum but i get the error:

from(bucket: “pv”)
|> range(start: dashboardTime)
|> filter(fn: ® => r._measurement == “2120198970” and r._field == “energy”)
|>sum()

Error: Panic: runtime error: invalid memory address or nill pointer deference

before applying sum(), i’ve got this raw data:

#group,false,false,true,true,false,false,true,true,true
#datatype,string,long,dateTime:RFC3339,dateTime:RFC3339,dateTime:RFC3339,double,string,string,string
#default,_result,
,result,table,_start,_stop,_time,_value,_field,_measurement,device
,0,2020-11-16T15:23:23.543388695Z,2020-11-16T21:23:23.543388695Z,2020-11-16T15:25:09Z,699,energy,2120198970,2120198970
,0,2020-11-16T15:23:23.543388695Z,2020-11-16T21:23:23.543388695Z,2020-11-16T15:30:09Z,699,energy,2120198970,2120198970
,0,2020-11-16T15:23:23.543388695Z,2020-11-16T21:23:23.543388695Z,2020-11-16T15:35:09Z,699,energy,2120198970,2120198970
,0,2020-11-16T15:23:23.543388695Z,2020-11-16T21:23:23.543388695Z,2020-11-16T15:40:08Z,699,energy,2120198970,2120198970
,0,2020-11-16T15:23:23.543388695Z,2020-11-16T21:23:23.543388695Z,2020-11-16T15:45:09Z,699,energy,2120198970,2120198970
,0,2020-11-16T15:23:23.543388695Z,2020-11-16T21:23:23.543388695Z,2020-11-16T15:50:08Z,699,energy,2120198970,2120198970
,0,2020-11-16T15:23:23.543388695Z,2020-11-16T21:23:23.543388695Z,2020-11-16T15:55:09Z,699,energy,2120198970,2120198970
,0,2020-11-16T15:23:23.543388695Z,2020-11-16T21:23:23.543388695Z,2020-11-16T16:00:08Z,699,energy,2120198970,2120198970
,0,2020-11-16T15:23:23.543388695Z,2020-11-16T21:23:23.543388695Z,2020-11-16T16:05:09Z,699,energy,2120198970,2120198970

I’ve tried with a count() or median() and it works so it looks like an issue with the sum()

I’m executing the command in Chronograf 1.8.5 and influxdb 1.8.3. on rpi4, raspbian 32bit

5

Hello @ced2flux,
I’m not sure.

import "csv"
csvData = "#group,false,false,true,true,false,false,true,true,true
#datatype,string,long,dateTime:RFC3339,dateTime:RFC3339,dateTime:RFC3339,double,string,string,string
#default,,,,,,,,,
,result,table,_start,_stop,_time,_value,_field,_measurement,device
,,0,2020-11-16T15:23:23.543388695Z,2020-11-16T21:23:23.543388695Z,2020-11-16T15:25:09Z,699,energy,2120198970,2120198970
,,0,2020-11-16T15:23:23.543388695Z,2020-11-16T21:23:23.543388695Z,2020-11-16T15:30:09Z,699,energy,2120198970,2120198970
,,0,2020-11-16T15:23:23.543388695Z,2020-11-16T21:23:23.543388695Z,2020-11-16T15:35:09Z,699,energy,2120198970,2120198970
,,0,2020-11-16T15:23:23.543388695Z,2020-11-16T21:23:23.543388695Z,2020-11-16T15:40:08Z,699,energy,2120198970,2120198970
,,0,2020-11-16T15:23:23.543388695Z,2020-11-16T21:23:23.543388695Z,2020-11-16T15:45:09Z,699,energy,2120198970,2120198970
,,0,2020-11-16T15:23:23.543388695Z,2020-11-16T21:23:23.543388695Z,2020-11-16T15:50:08Z,699,energy,2120198970,2120198970
,,0,2020-11-16T15:23:23.543388695Z,2020-11-16T21:23:23.543388695Z,2020-11-16T15:55:09Z,699,energy,2120198970,2120198970
,,0,2020-11-16T15:23:23.543388695Z,2020-11-16T21:23:23.543388695Z,2020-11-16T16:00:08Z,699,energy,2120198970,2120198970
,,0,2020-11-16T15:23:23.543388695Z,2020-11-16T21:23:23.543388695Z,2020-11-16T16:05:09Z,699,energy,2120198970,2120198970
"
csv.from(csv: csvData)
|>sum()

Works great for me. I did have to fix your Annotated CSV a little though by adding an extra column with , , . Does that Flux script work for you as well?

6

Hi,

Nope, it fails.

I’ve got the error:Error: Panic: Runtime error

I’ve tried with median or count and it works.

7

Hello @ced2flux,
I’m not sure what’s happening. Can you please submit an issue? I’ll send this along to the right people as well in case I’m missing something.