---
title: Aggregation API
slug: aggregation-api
icon: {"faIcon":"fa-solid fa-manat-sign"}
createdAt: 2025-06-10T06:44:31.749Z
---

## Overview

The Aggregate API offers a series of functions to execute aggregations on a Table, rather than utilising the Table API.  Whilst the Table API will still be used to get information from records, this API focuses much more on aggregate functions to make sure it is much more efficient and easier to use for specific purposes.

Tables can be accessed by using this generic notation:

`Aggregate('User')`

Where ‘User’ can be replaced with any Table name.

Alternatively it can be accessed by the Table api as well

`Table('User').getAggregate()`

## Querying data

### Aggregate operators

The following table is a list of the supported queries. For filtering, see Table Api

| Operators                                         |                                                                                                                                                                                                                                                                                                                  |
| ------------------------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| COUNT()                                           | The simplest operator implementing to count records. For example:<br />`Aggregate("User").COUNT()`<br />                                                                                                                                                                                                         |
| COUNT(fieldName)                                  | Count the non null occurrences of a field:<br />`Aggregate("User").COUNT("Email")`                                                                                                                                                                                                                               |
| DISTINCT(fieldName)                               | Performs a count of the Unique values (e.g. the count of the unique Companies referenced from the User table)<br />`Aggregate("User").DISTINCT("Company")`                                                                                                                                                       |
| SUM(fieldName)                                    | Count the non null occurrences of a field:<br />`Aggregate("User").SUM("FailedLoginAttempts")`                                                                                                                                                                                                                   |
| MIN(fieldName)                                    | Minimum of a field values:<br />`Aggregate("User").MIN("FailedLoginAttempts")`                                                                                                                                                                                                                                   |
| MAX(fieldName)                                    | Maximum of a field values:<br />`Aggregate("User").MAX("FailedLoginAttempts")`                                                                                                                                                                                                                                   |
| AVG(fieldName)                                    | Compute the average o field values:<br />`Aggregate("User").AVG("FailedLoginAttempts")`                                                                                                                                                                                                                          |
| HAVING( *CRITERION*(“AGGREGATE\_NAME“, *value*) ) | Allows aggregates to be filtered from the results:<br />`Aggregate("Trigger")    `<br />`.GROUP_BY("TriggerWhen", AS("When"))    `<br />`.COUNT("TriggerWhen", AS("Total")) // This is the Total column    `<br />`.HAVING(GT("Total", 60))   // Allow groups with 'Total' greater than 60    `<br />`.fetch();` |

 

Aggregations can be aliased:

:::BlockQuote
Aggregate("User").COUNT(As("UserCount");Aggregate("User").COUNT("Email", AS("EmailCount"))
:::

Aggregations can leverage the query operators from the Table Api

:::BlockQuote
Aggregate("User").COUNT().EQUAL("Active", true)Aggregate("User").COUNT().AND(  EQUAL("Active", true),  GT("FailedLoginAttempts", 0))
:::

## Grouping

You can add group by fields to the Aggregate:

```javascript
Aggregate("Trigger")
    .IN("Table", ["Incident", "ITSMRequest"])
    .GROUP_BY("Table")
    .GROUP_BY("TriggerWhen", AS("When"))
    .COUNT("TriggerWhen", AS("Total"))
    .fetch();
```

Results in:

```javascript
[
  {
    "TableGroup": "ITSMRequest",
    "TableGroupDisplayValue": "ITSM Request",
    "Total": 2,
    "When": "after",
    "WhenDisplayValue": "After"
  },
  {
    "TableGroup": "Incident",
    "Total": 6,
    "When": "before",
    "WhenDisplayValue": "Before"
  },
  {
    "TableGroup": "ITSMRequest",
    "TableGroupDisplayValue": "ITSM Request",
    "Total": 5,
    "When": "before",
    "WhenDisplayValue": "Before"
  },
  {
    "TableGroup": "Incident",
    "Total": 4,
    "When": "after",
    "WhenDisplayValue": "After"
  }
]
```

### Querying example

```javascript
let aggregateResult = Aggregate('SLA')
.COUNT(AS('Breached'))
.SUM("TargetDuration")
.EQUAL('ProgressLevel', 'Breached');
.fetchSingle();

// {Breached=4, TargetDurationSum=115320000}
aggregateResult;

// 4
aggregateResult.Breached;
```