Sum up multiple ranges in one column

InfluxDB 2
,
1

Hi,

I have a table with speed and brake values. Brake can be 1 or 0. In this table there are multiple sections where “brake” is 1, and I want to add up all speed spreadings.

It looks something like this

34 | 0
34 | 1
33 | 1
32 | 1
31 | 1
31 | 0
[...]
29 | 1
28 | 1
26 | 1
26 | 1

In this example I would like to add up the first spread (34-31 : 3) and the second spread (29-26: 3) and get 6 as a result.

I know how to do this for one range, is there a way how to do this for all?

Thank you!

2

Hello @hermann4,
Is your data in two tables? Can you share the query you use to get those speed and brake values so I can get a better feel for your schema?

Can you not apply the sum() function?

sum(column: "break")

If your data is in multiple tables, you might have to apply a group() function first to ungroup your data so you can sum across tables.

If you need to group by speed value you can use conditional query logic to apply a tag like so:

import "influxdata/influxdb/schema"
from(bucket: "my bucket")
 |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
 |> filter(fn: (r) => r["_measurement"] == "my measurement")
 |> filter(fn: (r) => r["_field"] == "speed")
|> map(fn: (r) => ({
    r with
    speed_level:
      if r._value >= 20.0 and r._value <= 30.0 then "20-30"
      else if r._value >= 30.0 and r._value <= 40.0 then "30-40"
      else "other speed"
    })
  )
|> group(columns: [speed_level"], mode:"by")
|> schema.fieldsAsCols()
|> sum(column: "break")
// this query will give you a separate table where each table is the sum for the range of speeds. if you wanted total sum from here you could group() and sum again.

I’m using the schema.fieldsAsCols() function to pivot the data so that I can get two columns with the values for the speed and the break. This might be all your asking for if your data is all in one table and you don’t want to perform any sort of speed range tagging.