# Sum in map function

**URL:** <https://community.influxdata.com/t/sum-in-map-function/23232>\
**Category:** Fluxlang\
**Created:** [January 5, 2022, 12:33pm UTC](https://community.influxdata.com/t/sum-in-map-function/23232 "2022-01-05T12:33:47Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![Patse](https://avatars.discourse-cdn.com/v4/letter/p/dbc845/32.png) [@Patse](https://community.influxdata.com/u/Patse)\
**Post date:** [January 5, 2022, 12:33pm UTC](https://community.influxdata.com/t/sum-in-map-function/23232/1 "2022-01-05T12:33:47Z")

</div>

Hey,  
i want to use the sum Function within the map() function but i only get the error:  
 ![image](https://us1.discourse-cdn.com/flex023/uploads/influxdata/original/2X/7/75a7e3cf0ddb81881dcf60bdeb1f4e12365f7c6b.png)  
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")

```

}))

Can anyone help me please?

---

<div class="post-metadata">

**Author:** ![scott](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/scott/32/16492_2.png) [@scott](https://community.influxdata.com/u/scott)\
**Post date:** [January 5, 2022, 5:50pm UTC](https://community.influxdata.com/t/sum-in-map-function/23232/2 "2022-01-05T17:50:29Z")

</div>

@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:

```javascript
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}))

```