# Aggregation first/last with reduce

Hi
I try to get multiple aggregation (sum, avg, min, max, first, last), so I found the function reduce. Works well, but I’m puzzled about first and last. It seems that count begins from last record, so I get last with accumulator.count == 0

Example:
from(bucket: “”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == “VM”)
|> filter(fn: (r) => r["_field"] == “vRAM”)
|> group(columns: [“name”])
|> reduce(identity: {
sum: 0.0, count: 0, avg: 0.0, min: 0.0, max: 0.0, first: 0.0, last: 0.0
},
fn: (r, accumulator) => ({
sum: accumulator.sum + r._value,
count: accumulator.count + 1,
avg: (accumulator.sum + r._value) / float(v: accumulator.count + 1),
min: if accumulator.count == 0 then r._value else if r._value < accumulator.min then r._value else accumulator.min,
max: if accumulator.count == 0 then r._value else if r._value > accumulator.max then r._value else accumulator.max,
first: if accumulator.count == accumulator.count then r._value else accumulator.first,
last: if accumulator.count == 0 then r._value else accumulator.last,
}),
)

Has somebody experience about first / last with reduce? Thanks in advance.

23.07.21:
Sometimes, sort-order of “_time” is in disorder, that’s why first/last was wrong. So I have to add “|> sort(columns:[”_time"])".

This happens if the device is not continuous available. This is working well now:
from(bucket: “”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == “VM”)
|> filter(fn: (r) => r["_field"] == “vRAM”)
|> group(columns: [“name”])
|> sort(columns:["_time"])
|> reduce(identity: {
sum: 0.0, count: 0, avg: 0.0, min: 0.0, max: 0.0, first: 0.0, last: 0.0
},
fn: (r, accumulator) => ({
sum: accumulator.sum + r._value,
count: accumulator.count + 1,
avg: (accumulator.sum + r._value) / float(v: accumulator.count + 1),
min: if accumulator.count == 0 then r._value else if r._value < accumulator.min then r._value else accumulator.min,
max: if accumulator.count == 0 then r._value else if r._value > accumulator.max then r._value else accumulator.max,
first: if accumulator.count == 0 then r._value else accumulator.first,
last: if accumulator.count == accumulator.count then r._value else accumulator.last,
}),
)

Hi and welcome.

could you wrap your code in </> code blocks , not doing so makes it hard to read.

Can you create a simpler example that demonstrates the issue/unexpected behavior.

1 Like

Hello @AlainFricker,
It’s also worth mentioning that reduce() really shines for custom functions.
Otherwise you should consider using the following functions

``````min()
max()
last()
count()
sum()
``````

and invoking multiple yields instead to preserve pushdown patterns and increase performance.
You can read about pushdown patterns here:

and here: