# Sum in map function

Hey,
i want to use the sum Function within the map() function but i only get the error:

At the end i want to divide the sum of two columns like this: sum(column1)/sum(column2)
My query at the moment looks like this:

from(bucket: “Bucket”)

|> range(start: -365d)

|> filter(fn: (r) =>

``````r._measurement =="Measurement" and

r._field == "value1" or

r._field == "Status" or

r._field == "value2"
``````

)

|> pivot(

``````rowKey:["_time"],

columnKey: ["_field"],

valueColumn: "_value"
``````

)

|>keep(columns: ["_time", “value1”, “Status”,“month”,“year”,“value2”])

|>fill(column: “Status”, usePrevious: true)

|> filter(fn: (r) =>

``````r.Status ==1
``````

)

|>drop(columns:[“Status”])

|>group(columns: [“month”, “year”])

|> map(fn: (r) => ({

``````  _time: r._time,

"1/2": sum(column: "value1")/sum(column: "value2")
``````

}))

@Patse `sum()` operates on a stream of tables, so you can’t use it inside of `map()`. Map iterates over and operates on each row as an individual record. What I’d suggest doing is creating separate streams of tables for each field (`value1`, `value2`, and `Status`) and window and aggregate those by month (so you don’t have to group by month and year). For `value1` and `value2`, you apply the `sum` aggregate and for `Status` you can apply the `last` selector with the `createEmpty` sent to `true` if there are months with no `Status` values. You can then union those streams together, pivot, filter, and divide the sum of `value1` by the sum of `value2` per month where `Status` is equal to `1`. It would look something like this:

``````data = from(bucket: "Bucket")
|> range(start: -365d)
|> filter(fn: (r) => r._measurement =="Measurement")

value1 = data |> filter(fn: (r) => r._field == "value1") |> aggregateWindow(every: 1mo, fn: sum)
value2 = data |> filter(fn: (r) => r._field == "value2") |> aggregateWindow(every: 1mo, fn: sum)
Status = data |> filter(fn: (r) => r._field == "Status") |> aggregateWindow(every: 1mo, fn: last, createEmpty: true) |> fill(usePrevious: true)

union(tables: [value1, value2, Status])
|> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
|> keep(columns: ["_time", "value1", "value2", "Status"])
|> filter(fn: (r) => r.Status == 1)
|> drop(columns:["Status"])
|> map(fn: (r) => ({_time: r._time, "1/2": r.value1/r.value2}))
``````
1 Like