Finding the Sum based on Group using Flux Query - InfluxDB

Question

I am trying to use flux query language to find the sum of a column based on a person. If I have the following input table:

How can I use a Flux Query to obtain the following output table:

I have tried something like this so far but I get errors:

from: (bucket: "example")
  |> range(start:v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r)=> r["_measurement"] == "test")
  |> group(columns: r["person"])
  |> reduce( fn: (r, accumulator) => ({sum: r._value + accumulator.sum}), identity: {sum: 0})

do u need flux query or mysql query. If I provide mysql query will you convert to flux query? — Rahul Biswas, Jan 19 '22 at 06:20
I need flux query. Is there an easy way to convert mysql query to flux query? — Dr.Dough, Jan 19 '22 at 06:26

score 5 · Accepted Answer · answered Jan 19 '22 at 15:19

You are almost on the right way. You have already grouped by name and now you need to use function sum. Pay attention on last function - |> group() it's just for union table to one view.

|> group(columns: ["person"])
|> sum(column: "hoursSpent")
|> group()

I provide full of my query for debug:

import "array"

data = array.from(rows: [
  {person: "John Smith", sport: "Cycling", hoursSpent: 5},
  {person: "John Smith", sport: "Hiking", hoursSpent: 6},
  {person: "John Smith", sport: "Swimming", hoursSpent: 1},
  {person: "John Smith", sport: "Dancing", hoursSpent: 2},
  {person: "Nancy Jones", sport: "Badminton", hoursSpent: 10},
  {person: "Nancy Jones", sport: "Soccer", hoursSpent: 31},
  {person: "Nancy Jones", sport: "Basketball", hoursSpent: 8},
  {person: "Trevor John", sport: "Baseball", hoursSpent: 24},
  {person: "Trevor John", sport: "Water Polo", hoursSpent: 2},
])

data
 |> group(columns: ["person"])
 |> sum(column: "hoursSpent")
 |> group()

What should I do if I want |> sum(column: "a"), sum(column: "b"). call sum twice one time — DinoStray, Mar 25 '23 at 16:15
@DinoStray can you provide a little example of data and what you expected? — Amerousful, Mar 25 '23 at 16:47

Finding the Sum based on Group using Flux Query - InfluxDB

1 Answers1