> ## Documentation Index
> Fetch the complete documentation index at: https://docs.activeviam.com/llms.txt
> Use this file to discover all available pages before exploring further.

# User Defined Aggregation Functions

DirectQuery delegates some aggregation work to the remote database.
The core ActivePivot aggregation (such as `SUM`, `AVG`, `MAX`...)  are natively converted to the remote database equivalent aggregations.

However, if you write some [custom aggregation functions](../../copper/copper_measures#aggregated-measures-with-user-defined-aggregate-functions-udaf) (UDAF) you will also need to define how this aggregation is done in your remote database.

## Sum product example

This example is based on a ClickHouse database and SQL but can easily be ported to other databases.

For instance let's consider a simple sum product on scalar values, which in SQL would be `SUM(quantity * price)`.

Here is a simple implementation with Copper:

```java theme={"languages":{"custom":["/engine/python-sdk/6.2/languages/pycon.tmLanguage.json"]}}
Copper.userDefinedAgg("QUANTITY", "UNIT_PRICE")
    .aggregationBuffer(Types.TYPE_DOUBLE)
    .pluginKey("MY_SUM_PRODUCT")
    .contribute((reader, agg) -> agg.addDouble(0, reader.readDouble(0) * reader.readDouble(1)))
    .merge((in, out) -> out.addDouble(0, in.readDouble(0)))
    .outputFromBuffer(0)
    .as("My Sum Product")
    .publish(context);
```

When converting the query to SQL for ClickHouse, DirectQuery will look for an aggregation function with the same plugin key.
This aggregation function will convert the fields to SQL code.

We simply need to register the aggregation function, with the plugin key we use in Atoti, the number of inputs (i.e.
the number of SQL columns to read), the list of functions to write the SQL expression
from the list of the column names and their output type.
The number of functions (and their output type) should be the same as the number of inputs expected by the Copper
function you just registered.

```java theme={"languages":{"custom":["/engine/python-sdk/6.2/languages/pycon.tmLanguage.json"]}}
session.registerSqlUdaf(
    "MY_SUM_PRODUCT",
    2,
    List.of(
        sqlColumnNames -> "SUM(" + sqlColumnNames.get(0) + " * " + sqlColumnNames.get(1) + ")"),
    List.of(StandardTypes.DOUBLE));
```

This new Aggregation can now be used in a DirectQuery project and the computation will be pushed to the remote database.

```java theme={"languages":{"custom":["/engine/python-sdk/6.2/languages/pycon.tmLanguage.json"]}}
final ICellSet result =
    ActivePivotQueryRunner.create()
        .withWildcardCoordinate("DATE")
        .forMeasures("My Sum Product")
        .run(pivot);
GetAggregatesResultCellset.check(pivot, result).getHumanRepresentationOfCellSet();
```

| DATE       | My Sum Product |
| ---------- | -------------- |
| 2022-02-03 | 580.0          |
| 2022-01-31 | 798.0          |
| 2022-02-04 | 258.0          |
| 2022-02-01 | 1030.0         |
| 2022-02-02 | 604.0          |