---
title: "Aggregate"
slug: "aggregate"
updated: 2022-03-31T18:21:58Z
published: 2022-03-31T18:21:58Z
canonical: "docs.datajet.app/aggregate"
---

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

# Aggregate

## Overview

![](https://cdn.document360.io/73b6469b-c1e4-47e9-aa65-986c8ebd17ca/Images/Documentation/image-1648749041887.png)

Aggregating combines all records for each unique join-key on a foreign table in some manner, and creates that entity as a field on the primary side of the join.

Consider the relationship above, each customer may have multiple transactions on the transactions table.

![](https://cdn.document360.io/73b6469b-c1e4-47e9-aa65-986c8ebd17ca/Images/Documentation/image-1648749323151.png)

Here, the customer with id 1000 has 3 transactions.

We can create a field on the customer table for total profit above with the definition above. This is a **sum** aggregation.

The JSON look like

```json
{
  "method": "BuildAggregate",
  "project": "MyProject",
  "targetTable": "customer",
  "source": "transactions.profit",
  "overwrite": true,
  "name": "total profit",
  "function": "sum"
}
```

**function** can be count, min, max, avg or sum

![](https://cdn.document360.io/73b6469b-c1e4-47e9-aa65-986c8ebd17ca/Images/Documentation/image-1648750027770.png)

The transactions table can be pre-filtered with a DataSet, only those transactions included in this DataSet will contribute to the calculation.

So setting a DataSet of product-id like "G*" for a sum aggregate called "G product profit"

![](https://cdn.document360.io/73b6469b-c1e4-47e9-aa65-986c8ebd17ca/Images/Documentation/image-1648750299368.png)

```json
{
  "method": "BuildAggregate",
  "targetTable": "customer",
  "source": "transactions.profit",
  "overwrite": true,
  "name": "G product profit",
  "function": "sum",
  "project": "MyProject",
  "dataSet": {
    "logic": "or",
    "name": "DataSet",
    "strict": true,
    "set": [
      {
        "logic": "and",
        "stype": "FIELD",
        "entity": {
          "type": "field",
          "name": "transactions.product-id",
          "table": "transactions",
          "valueFilter": []
        },
        "op": "like",
        "values": [
          "G*"
        ]
      }
    ]
  }
}
```

Aggregates can be combined with other engineering functions to product other useful entities on the primary table.

For Example, if we create a "first purchase" and a "last purchase" field on the customer table, by performing a min and max aggregate on the transaction date

we can use an Expression Field "**[last purchase]-[first purchase]**" to produce a lifetime in days.

This could be divided by the number of transactions (count aggregate) to produce a "frequency of purchase" field.

![](https://cdn.document360.io/73b6469b-c1e4-47e9-aa65-986c8ebd17ca/Images/Documentation/image-1648750912732.png)