# Create custom aggregate functions

Warning! This page documents an earlier version of Flux, which is no longer actively developed. Flux v0.50 is the most recent stable version of Flux.

To aggregate your data, use the Flux built-in aggregate functions or create custom aggregate functions using the `reduce()`function.

## Aggregate function characteristics

Aggregate functions all have the same basic characteristics:

• They operate on individual input tables and transform all records into a single record.
• The output table has the same group key as the input table.

## How reduce() works

The `reduce()` function operates on one row at a time using the function defined in the `fn` parameter. The `fn` function maps keys to specific values using two objects specified by the following parameters:

Parameter Description
`r` An object that represents the row or record.
`accumulator` An object that contains values used in each row’s aggregate calculation.

The `reduce()` function’s `identity` parameter defines the initial `accumulator` object.

### Example reduce() function

The following example `reduce()` function produces a sum and product of all values in an input table.

``````|> reduce(fn: (r, accumulator) => ({
sum: r._value + accumulator.sum,
product: r._value * accumulator.product
})
identity: {sum: 0.0, product: 1.0}
)
``````

To preserve existing columns, use the `with` operator when mapping values in the `r` object.

To illustrate how this function works, take this simplified table for example:

``````                  _time   _value
-----------------------  -------
2019-04-23T16:10:49.00Z      1.6
2019-04-23T16:10:59.00Z      2.3
2019-04-23T16:11:09.00Z      0.7
2019-04-23T16:11:19.00Z      1.2
2019-04-23T16:11:29.00Z      3.8``````
###### Input objects

The `fn` function uses the data in the first row to define the `r` object. It defines the `accumulator` object using the `identity` parameter.

``````r           = { _time: 2019-04-23T16:10:49.00Z, _value: 1.6 }
accumulator = { sum  : 0.0, product : 1.0 }
``````
###### Key mappings

It then uses the `r` and `accumulator` objects to populate values in the key mappings:

``````// sum: r._value + accumulator.sum
sum: 1.6 + 0.0

// product: r._value * accumulator.product
product: 1.6 * 1.0
``````
###### Output object

This produces an output object with the following key value pairs:

``````{ sum: 1.6, product: 1.6 }
``````

The function then processes the next row using this output object as the `accumulator`.

Because `reduce()` uses the output object as the `accumulator` when processing the next row, keys mapped in the `fn` function must match keys in the `identity` and `accumulator` objects.

###### Processing the next row
``````// Input objects for the second row
r           = { _time: 2019-04-23T16:10:59.00Z, _value: 2.3 }
accumulator = { sum  : 1.6, product : 1.6 }

// Key mappings for the second row
sum: 2.3 + 1.6
product: 2.3 * 1.6

// Output object of the second row
{ sum: 3.9, product: 3.68 }
``````

It then uses the new output object as the `accumulator` for the next row. This cycle continues until all rows in the table are processed.

##### Final output object and table

After all records in the table are processed, `reduce()` uses the final output object to create a transformed table with one row and columns for each mapped key.

``````// Final output object
{ sum: 9.6, product: 11.74656 }

// Output table
sum    product
----  ---------
9.6   11.74656
``````

#### What happened to the _time column?

The `reduce()` function only keeps columns that are:

1. Are part of the input table’s group key.
2. Explicitly mapped in the `fn` function.

It drops all other columns. Because `_time` is not part of the group key and is not mapped in the `fn` function, it isn’t included in the output table.

## Custom aggregate function examples

To create custom aggregate functions, use principles outlined in Creating custom functions and the `reduce()` function to aggregate rows in each input table.

### Create a custom average function

This example illustrates how to create a function that averages values in a table. This is meant for demonstration purposes only. The built-in `mean()` function does the same thing and is much more performant.

``````average = (tables=<-, outputField="average") =>
tables
|> reduce(
// Define the initial accumulator object
identity: {
count: 1.0,
sum:   0.0,
avg:   0.0
}
fn: (r, accumulator) => ({
// Increment the counter on each reduce loop
count: accumulator.count + 1.0,
// Add the _value to the existing sum
sum:   accumulator.sum + r._value,
// Divide the existing sum by the existing count for a new average
avg:   accumulator.sum / accumulator.count
})
)
// Drop the sum and the count columns since they are no longer needed
|> drop(columns: ["sum", "count"])
// Set the _field column of the output table to to the value
// provided in the outputField parameter
|> set(key: "_field", value: outputField)
// Rename avg column to _value
|> rename(columns: {avg: "_value"})
``````
``````average = (tables=<-, outputField="average") =>
tables
|> reduce(
identity: {
count: 1.0,
sum:   0.0,
avg:   0.0
}
fn: (r, accumulator) => ({
count: accumulator.count + 1.0,
sum:   accumulator.sum + r._value,
avg:   accumulator.sum / accumulator.count
})
)
|> drop(columns: ["sum", "count"])
|> set(key: "_field", value: outputField)
|> rename(columns: {avg: "_value"})
``````

### Aggregate multiple columns

Built-in aggregate functions only operate on one column. Use `reduce()` to create a custom aggregate function that aggregates multiple columns.

The following function expects input tables to have `c1_value` and `c2_value` columns and generates an average for each.

``````multiAvg = (tables=<-) =>
tables
|> reduce(
identity: {
count:  1.0,
c1_sum: 0.0,
c1_avg: 0.0,
c2_sum: 0.0,
c2_avg: 0.0
}
fn: (r, accumulator) => ({
count:  accumulator.count + 1.0,
c1_sum: accumulator.c1_sum + r.c1_value,
c1_avg: accumulator.c1_sum / accumulator.count,
c2_sum: accumulator.c2_sum + r.c2_value,
c2_avg: accumulator.c2_sum / accumulator.count
})
)
``````

### Aggregate gross and net profit

Use `reduce()` to create a function that aggregates gross and net profit. This example expects `profit` and `expenses` columns in the input tables.

``````profitSummary = (tables=<-) =>
tables
|> reduce(
identity: {
gross: 0.0,
net:   0.0
}
fn: (r, accumulator) => ({
gross: accumulator.gross + r.profit,
net:   accumulator.net + r.profit - r.expenses
})
)
``````
{"ft_lang_label":"__label__en","ft_lang_prob":0.550034,"math_prob":0.9684137,"size":5908,"snap":"2019-51-2020-05","text_gpt3_token_len":1590,"char_repetition_ratio":0.1839431,"word_repetition_ratio":0.078663796,"special_character_ratio":0.2999323,"punctuation_ratio":0.20889261,"nsfw_num_words":0,"has_unicode_error":false,"math_prob_llama3":0.99328166,"pos_list":[0],"im_url_duplicate_count":[null],"WARC_HEADER":"{\"WARC-Type\":\"response\",\"WARC-Date\":\"2020-01-26T04:48:12Z\",\"WARC-Record-ID\":\"<urn:uuid:a0d55cf2-2b3b-4a2d-931a-9c809ff207fd>\",\"Content-Length\":\"93662\",\"Content-Type\":\"application/http; msgtype=response\",\"WARC-Warcinfo-ID\":\"<urn:uuid:42d70408-47ba-42ab-a906-989f0e7e09c8>\",\"WARC-Concurrent-To\":\"<urn:uuid:62f6003a-be47-49d1-989e-0e48e592bd03>\",\"WARC-IP-Address\":\"13.249.44.55\",\"WARC-Target-URI\":\"https://docs.influxdata.com/flux/v0.36/functions/custom-functions/custom-aggregate/\",\"WARC-Payload-Digest\":\"sha1:MZ6MNV7R6CWCQZUOENGIK5ZY3XR25OM7\",\"WARC-Block-Digest\":\"sha1:BJ4ZQF7QGWOVB5WPVJM2TZ7DE23K3QLR\",\"WARC-Identified-Payload-Type\":\"text/html\",\"warc_filename\":\"/cc_download/warc_2020/CC-MAIN-2020-05/CC-MAIN-2020-05_segments_1579251687725.76_warc_CC-MAIN-20200126043644-20200126073644-00141.warc.gz\"}"}