# Couldn't reduce with pivot and group

**URL:** <https://community.influxdata.com/t/couldnt-reduce-with-pivot-and-group/23643>\
**Category:** Fluxlang\
**Tags:** flux, aggregate\
**Created:** [February 1, 2022, 8:15pm UTC](https://community.influxdata.com/t/couldnt-reduce-with-pivot-and-group/23643 "2022-02-01T20:15:07Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![Duck](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/duck/32/6747_2.png) [@Duck](https://community.influxdata.com/u/Duck)\
**Post date:** [February 1, 2022, 8:15pm UTC](https://community.influxdata.com/t/couldnt-reduce-with-pivot-and-group/23643/1 "2022-02-01T20:15:07Z")

</div>

**Below code** is perfectly working and separating users table with “pivot” and “group”.

```auto
from(bucket:"statistics:daily")
    |> range(start:-1d)
    |> filter(fn: (r) => r["_measurement"] == "players")
    |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
    |> group(columns: ["user"], mode: "by")

```

but whenever I try to “reduce” it, Flux says the fields are empty.

**Latest code**

```auto
from(bucket:"statistics:daily")
    |> range(start:-1d)
    |> filter(fn: (r) => r["_measurement"] == "players")
    |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
    |> group(columns: ["user"], mode: "by")
    |> reduce(fn: (r, accumulator) => ({
                    user: r.user,
                    _time: r._time,
                    _measurement: r._measurement,
                    _field: r._field,
                    _value: float(v: r._value) + accumulator._value
    }), identity: {_time: now(), _measurement: "", user: "", _field: "", _value: 0.0})

```

**Latest code error** _(NOTE: There are no empty fields or values, I’m sure about it. If you use this code without reducing, it’d work as expected.)_

> runtime error @6:8-12:87: reduce: null values are not supported for “\_field” in the reduce() function

Even I check and set the default “field”, **Flux** continues to say “null for \_value”. I guess it drops all the things whenever I switch to “reduce” aggregate.

Is it impossible to use **reduce** with **pivot** and **group**?

---

<div class="post-metadata">

**Author:** ![Jay\_Clifford](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/jay_clifford/32/8287_2.png) [@Jay\_Clifford](https://community.influxdata.com/u/Jay_Clifford)\
**Post date:** [February 2, 2022, 10:54am UTC](https://community.influxdata.com/t/couldnt-reduce-with-pivot-and-group/23643/2 "2022-02-02T10:54:42Z")

</div>

Hi @Duck,  
This is due to the fact that `_field` and `_value` are considered null when you pivot by field. let’s take a look at my current data:

 ![Screenshot 2022-02-02 at 10.44.43](https://us1.discourse-cdn.com/flex023/uploads/influxdata/original/2X/f/f35da19ea57a76682e099a7c60aecaecfa503105.png)

Before the pivot you can see \_field and \_value are represented accordingly and would work against your reduce. After the pivot toy can see we drop the \_field and \_vaue field. These are replaced by the column jetson\_CPU1 with the values represented in this field.

Here is an example of using reduce with a pivot before.

```auto
raw = from(bucket: "Jetson")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "exec_jetson_stats")
  |> filter(fn: (r) => r["_field"] == "jetson_CPU1")
  |> last()
  |> yield(name: "before_pivot")
  |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
  |> yield(name: "After_pivot")

raw
    |> reduce(fn: (r, accumulator) => ({
                    _time: r._time,
                    _measurement: r._measurement,
                    jetson_CPU1: r.jetson_CPU1,
                    _value: float(v: r.jetson_CPU1) + accumulator._value
    }), identity: {_time: now(), _measurement: "", jetson_CPU1: 0.0, _value: 0.0})
      |> yield(name: "After_reduce")

```

Note if you wanted to do it your way which is perfectly reasonable then you should pivot after reduce.

---

<div class="post-metadata">

**Author:** ![Duck](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/duck/32/6747_2.png) [@Duck](https://community.influxdata.com/u/Duck)\
**Post date:** [February 2, 2022, 11:25am UTC](https://community.influxdata.com/t/couldnt-reduce-with-pivot-and-group/23643/3 "2022-02-02T11:25:38Z")

</div>

Thank you so much for the fast answer, it helped me. I’ve figured out how to deal with it and write an aggregation in Java language.

The problem I am facing now is to use **to** to transfer my data to another bucket. After using **pivot** , **group** , and **reduce** , it is almost impossible to transfer my grouped and reduced data( **Example:** ‘\_result, user=1’ and 'result, user=2).

Is there a way to transfer aggregated data to another bucket? I can do it but it just gets the latest field. For example, if I have 3 users grouped by the “user” field, it writes “user3” only, not “user1” and “user2”.

**BEFORE** _(bucket: daily)_

 ![image](https://us1.discourse-cdn.com/flex023/uploads/influxdata/original/2X/0/089ddb62cebc39868478f623ba56154ae46d1a1e.png)

**AFTER** _(bucket: overall)_

 ![image](https://us1.discourse-cdn.com/flex023/uploads/influxdata/original/2X/5/5afa8f1a7cc7fa671db4c3d0a88f033ab5c15bc2.png)

---

<div class="post-metadata">

**Author:** ![Jay\_Clifford](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/jay_clifford/32/8287_2.png) [@Jay\_Clifford](https://community.influxdata.com/u/Jay_Clifford)\
**Post date:** [February 2, 2022, 12:22pm UTC](https://community.influxdata.com/t/couldnt-reduce-with-pivot-and-group/23643/4 "2022-02-02T12:22:04Z")

</div>

No problem at all. Could you try ungrouping before the to() function? So essentially do reduce() then

```auto
|> group() //ungroup 

```

then do to().

---

<div class="post-metadata">

**Author:** ![Duck](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/duck/32/6747_2.png) [@Duck](https://community.influxdata.com/u/Duck)\
**Post date:** [February 2, 2022, 12:29pm UTC](https://community.influxdata.com/t/couldnt-reduce-with-pivot-and-group/23643/5 "2022-02-02T12:29:37Z")

</div>

Ungrouping works but the result is the same. It just wrote the last user instead of all users.

**BEFORE**

 ![image](https://us1.discourse-cdn.com/flex023/uploads/influxdata/original/2X/8/8e543dd6a0441713cf14d5392a736dbdb9841fa9.png)

**AFTER**

 ![image](https://us1.discourse-cdn.com/flex023/uploads/influxdata/original/2X/3/3d25f93a20e64d48180724cf4080705556d82d76.png)

---

<div class="post-metadata">

**Author:** ![Duck](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/duck/32/6747_2.png) [@Duck](https://community.influxdata.com/u/Duck)\
**Post date:** [February 2, 2022, 1:01pm UTC](https://community.influxdata.com/t/couldnt-reduce-with-pivot-and-group/23643/6 "2022-02-02T13:01:00Z")

</div>

I’ve kind of solved with using **experimental.to** without regrouping.

![image](https://us1.discourse-cdn.com/flex023/uploads/influxdata/original/2X/6/635ea1a6712059f28467262ce62addd5d5cfb69a.png)

---

<div class="post-metadata">

**Author:** ![Duck](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/duck/32/6747_2.png) [@Duck](https://community.influxdata.com/u/Duck)\
**Post date:** [February 2, 2022, 1:21pm UTC](https://community.influxdata.com/t/couldnt-reduce-with-pivot-and-group/23643/7 "2022-02-02T13:21:07Z")

</div>

After taking a look at it, it is **not solved**. It is working as I want but **user field** becomes **tag**. If I regroup with just measurement, it just resulted as same as first time(getting only last player, u\_2)