# Aggregation Expressions

Canonical page: https://www.adaptabletools.com/docs/adaptable-ql-expression-aggregation

- Aggregation Expressions evaluate against a **set of rows** and return a single value
- Aggregation Functions always use curly braces: `SUM{[PnL]}` — never parentheses
- The return value is either **scalar** (used in [Calculated Columns](https://www.adaptabletools.com/docs/handbook-calculated-column/index.md)) or **boolean** (used in [Alerts](https://www.adaptabletools.com/docs/handbook-alerting/index.md) e.g. Limits Management)
- Scope can be narrowed with `GROUP_BY` (a group of rows) and `WHERE` (a Standard Boolean Expression)
- Excel-style Filtered Aggregations (`SUMIF`, `COUNTIF`, `AVGIF`) bake the filter into the Function itself
- The value aggregated can be a calculation (e.g. `SUM{[Price] * [Quantity]}`)
- Aggregations can be combined with each other, and with values in the current row (e.g. % of group, z-scores)
- 3 specialist forms of Aggregations are also available: [Cumulative](https://www.adaptabletools.com/docs/adaptable-ql-expression-cumulative/index.md), [Quantile](https://www.adaptabletools.com/docs/adaptable-ql-expression-quantile/index.md) and [Window](https://www.adaptabletools.com/docs/adaptable-ql-expression-window/index.md)

Aggregation Expressions are [Expressions](https://www.adaptabletools.com/docs/adaptable-ql-expression/index.md) that run against **aggregated data**.

The Expression evaluates to a single value for each row, but that value is derived by aggregating multiple rows.

- **Aggregates every row**: each trade can show Desk's total, its share of that total, or how unusual its PnL is (z-score)
- **No need to group the Grid**: `GROUP_BY` splits the aggregation, while the Grid stays flat (or is grouped any other way)
- **Conditions on totals**: fire an Alert when `SUM{[PnL]} < '-500K'`, or when any Desk's Notional breaches its limit
- **Filter inside the Aggregation**: `WHERE` and Excel-style `SUMIF` / `COUNTIF` / `AVGIF` aggregate just the rows you want
- **Combine freely**: mix Aggregations with each other and with row values - `SUM{[PnL]} / SUM{[Notional]} * 100`
- **Always live**: every value recalculates as the data ticks

- AG Grid's aggregations fill group and total rows, and its formulas can only sum a fixed range of rows
- This means that you cannot create queries such as "every trade in this row's Desk"

**Example: AdapTableQL: Combining Aggregations**

Combining Aggregations with row values, WHERE and Filtered Aggregations

- This demo uses a set of trades and 5 Aggregated [Calculated Columns](https://www.adaptabletools.com/docs/handbook-calculated-column-aggregated/index.md):
  - `% of Desk` - each trade's share of its Desk's Notional: `[notional] / SUM{[notional], GROUP_BY([desk])} * 100`
  - `P&L Z-Score` - how unusual each P&L is: `([pnl] - AVG{[pnl]}) / STD_DEVIATION{[pnl]}`
  - `Desk Net Notional` - Buy minus Sell per Desk, using `WHERE`: `SUM{[notional] WHERE [side] = 'Buy', GROUP_BY([desk])} - SUM{[notional] WHERE [side] = 'Sell', GROUP_BY([desk])}`
  - `Open P&L` - `SUMIF{[pnl], [status] = 'Open'}`
  - `Open Trades` - `COUNTIF{[id], [status] = 'Open'}`
- A Format Column highlights any P&L more than 2 standard deviations from the average (`ABS([pnlZScore]) > 2`)

- See trade 9 (`NVDA`, P&L 25,000) highlighted as an outlier
- Change trade 9's `P&L` to 500 - the highlight disappears, and every Z-Score, `Open P&L` value updates
- Change a trade's `Side` and watch `Desk Net Notional` change for every trade in that Desk

Demo project (Vanilla); the files without a marker are shown below:

```text
src/
  adaptableOptions.ts
  agGridModules.ts     [template]
  columnDefs.ts
  gridOptions.ts
  index.ts             [template]
  onAdaptableReady.ts  [template]
  rowData.ts
  styles.css           [template]
index.html             [template]
package.json           [template]
tsconfig.json          [template]
```

Files marked `[template]` are the same in every demo; see the [demo template](https://www.adaptabletools.com/llms/demo-template.md#vanilla).

**`src/adaptableOptions.ts`**

```ts
import {AdaptableOptions} from '@adaptabletools/adaptable';
import {Trade} from './rowData';

export const adaptableOptions: AdaptableOptions<Trade> = {
  primaryKey: 'id',
  adaptableId: 'Combining Aggregations',
  initialState: {
    Dashboard: {
      ModuleButtons: ['CalculatedColumn', 'FormatColumn'],
    },
    Theme: {CurrentTheme: 'dark'},
    CalculatedColumn: {
      CalculatedColumns: [
        {
          ColumnId: 'deskShare',
          FriendlyName: '% of Desk',
          Expression: '[notional] / SUM{[notional], GROUP_BY([desk])} * 100',
          CalculatedColumnSettings: {DataType: 'number'},
        },
        {
          ColumnId: 'pnlZScore',
          FriendlyName: 'P&L Z-Score',
          Expression: '([pnl] - AVG{[pnl]}) / STD_DEVIATION{[pnl]}',
          CalculatedColumnSettings: {DataType: 'number'},
        },
        {
          ColumnId: 'deskNet',
          FriendlyName: 'Desk Net Notional',
          Expression:
            "SUM{[notional] WHERE [side] = 'Buy', GROUP_BY([desk])} - SUM{[notional] WHERE [side] = 'Sell', GROUP_BY([desk])}",
          CalculatedColumnSettings: {DataType: 'number'},
        },
        {
          ColumnId: 'openPnl',
          FriendlyName: 'Open P&L',
          Expression: "SUMIF{[pnl], [status] = 'Open'}",
          CalculatedColumnSettings: {DataType: 'number'},
        },
        {
          ColumnId: 'openTrades',
          FriendlyName: 'Open Trades',
          Expression: "COUNTIF{[id], [status] = 'Open'}",
          CalculatedColumnSettings: {DataType: 'number'},
        },
      ],
    },
    FormatColumn: {
      FormatColumns: [
        {
          Name: 'pnl-outlier',
          Scope: {ColumnIds: ['pnl', 'pnlZScore']},
          Rule: 'ABS([pnlZScore]) > 2',
          Style: {BackColor: '#f9a825', ForeColor: 'var(--ab-color-palette-13)', FontWeight: 'Bold'},
        },
        {
          Name: 'two-decimals',
          Scope: {ColumnIds: ['price', 'notional', 'deskShare', 'pnlZScore', 'deskNet']},
          DisplayFormat: {Formatter: 'NumberFormatter', Options: {FractionDigits: 2}},
        },
        {
          Name: 'trade-date',
          Scope: {ColumnIds: ['tradeDate']},
          DisplayFormat: {Formatter: 'DateFormatter', Options: {Pattern: 'dd MMM yyyy'}},
        },
      ],
    },
    Layout: {
      CurrentLayout: 'Trades',
      Layouts: [
        {
          Name: 'Trades',
          TableColumns: [
            'id',
            'ticker',
            'desk',
            'side',
            'notional',
            'deskShare',
            'deskNet',
            'pnl',
            'pnlZScore',
            'status',
            'openPnl',
            'openTrades',
          ],
          AutoSizeColumns: true,
        },
      ],
    },
  },
};
```

**`src/rowData.ts`**

```ts
export interface Trade {
  id: number;
  tradeDate: Date;
  ticker: string;
  desk: string;
  side: 'Buy' | 'Sell';
  quantity: number;
  price: number;
  notional: number;
  pnl: number;
  status: 'Open' | 'Closed';
}

type TradeInput = Omit<Trade, 'tradeDate' | 'notional'>;

const trades: TradeInput[] = [
  {id: 1, ticker: 'AAPL', desk: 'Tech', side: 'Buy', quantity: 500, price: 189.2, pnl: 1200, status: 'Open'},
  {id: 2, ticker: 'MSFT', desk: 'Tech', side: 'Buy', quantity: 200, price: 415.5, pnl: 850, status: 'Closed'},
  {id: 3, ticker: 'NVDA', desk: 'Tech', side: 'Sell', quantity: 100, price: 875.0, pnl: -2100, status: 'Open'},
  {id: 4, ticker: 'JPM', desk: 'Financials', side: 'Buy', quantity: 400, price: 198.7, pnl: 640, status: 'Open'},
  {id: 5, ticker: 'XOM', desk: 'Energy', side: 'Buy', quantity: 800, price: 118.2, pnl: -320, status: 'Closed'},
  {id: 6, ticker: 'AAPL', desk: 'Tech', side: 'Sell', quantity: 300, price: 190.1, pnl: 270, status: 'Closed'},
  {id: 7, ticker: 'GS', desk: 'Financials', side: 'Sell', quantity: 150, price: 452.3, pnl: -950, status: 'Open'},
  {id: 8, ticker: 'CVX', desk: 'Energy', side: 'Buy', quantity: 350, price: 156.4, pnl: 410, status: 'Open'},
  {id: 9, ticker: 'NVDA', desk: 'Tech', side: 'Buy', quantity: 250, price: 868.4, pnl: 25000, status: 'Open'},
  {id: 10, ticker: 'JPM', desk: 'Financials', side: 'Sell', quantity: 200, price: 199.9, pnl: 240, status: 'Closed'},
  {id: 11, ticker: 'XOM', desk: 'Energy', side: 'Sell', quantity: 600, price: 119.0, pnl: 480, status: 'Open'},
  {id: 12, ticker: 'MSFT', desk: 'Tech', side: 'Buy', quantity: 150, price: 417.2, pnl: -380, status: 'Open'},
  {id: 13, ticker: 'GS', desk: 'Financials', side: 'Buy', quantity: 100, price: 449.8, pnl: 520, status: 'Closed'},
  {id: 14, ticker: 'CVX', desk: 'Energy', side: 'Sell', quantity: 250, price: 157.1, pnl: -175, status: 'Closed'},
  {id: 15, ticker: 'AAPL', desk: 'Tech', side: 'Buy', quantity: 400, price: 188.7, pnl: 900, status: 'Open'},
  {id: 16, ticker: 'JPM', desk: 'Financials', side: 'Buy', quantity: 300, price: 197.6, pnl: -460, status: 'Open'},
];

// One trade a day from 1 September, each at a different time
export const rowData: Trade[] = trades.map((trade, index) => ({
  ...trade,
  tradeDate: new Date(2026, 8, index + 1, 9, 30 + index * 7),
  notional: Math.round(trade.quantity * trade.price * 100) / 100,
}));
```

**`src/columnDefs.ts`**

```ts
import {ColDef} from 'ag-grid-enterprise';
import {Trade} from './rowData';

export const columnDefs: ColDef<Trade>[] = [
  {field: 'id', headerName: 'Trade Id', cellDataType: 'number'},
  {field: 'tradeDate', headerName: 'Trade Date', cellDataType: 'date'},
  {field: 'ticker', headerName: 'Ticker', cellDataType: 'text'},
  {field: 'desk', headerName: 'Desk', cellDataType: 'text', enableRowGroup: true},
  {field: 'side', headerName: 'Side', cellDataType: 'text'},
  {field: 'quantity', headerName: 'Quantity', cellDataType: 'number', type: 'rightAligned'},
  {field: 'price', headerName: 'Price', cellDataType: 'number', type: 'rightAligned'},
  {field: 'notional', headerName: 'Notional', cellDataType: 'number', type: 'rightAligned'},
  {field: 'pnl', headerName: 'P&L', cellDataType: 'number', type: 'rightAligned'},
  {field: 'status', headerName: 'Status', cellDataType: 'text'},
];
```

**`src/gridOptions.ts`**

```ts
import {GridOptions, themeBalham} from 'ag-grid-enterprise';

import {columnDefs} from './columnDefs';
import {rowData, Trade} from './rowData';

export const gridOptions: GridOptions<Trade> = {
  theme: themeBalham,
  defaultColDef: {
    resizable: true,
    sortable: true,
    editable: true,
    filter: true,
    floatingFilter: true,
  },
  columnDefs: columnDefs,
  rowData: rowData,
  sideBar: true,
  suppressMenuHide: true,
  columnMenu: 'new',
  cellSelection: true,
  statusBar: {
    statusPanels: [
      {statusPanel: 'agTotalRowCountComponent', align: 'left'},
      {statusPanel: 'agFilteredRowCountComponent'},
      {
        key: 'Center Panel',
        statusPanel: 'AdaptableStatusPanel',
        align: 'center',
      },
    ],
  },
};
```

- Write Aggregation Functions with **curly braces** (as opposed to parentheses used with Standard Functions)

```ts [[2, 2, "SUM"], [1, 2, "PnL"], [2, 4, "SUM"], [4, 4, "1"], [4, 4, "2"], [4, 4, "3"]]
// Aggregation — sum of the PnL column across rows - contains curly braces
SUM{[PnL]};
// Standard — add 3 numbers in the current row - contains parentheses
SUM(1, 2, 3);
```

| Type        | Returns       | Available in Modules                                                       |
| ----------- | ------------- | -------------------------------------------------------------------------- |
| **Scalar**  | Any value     | [Calculated Column](https://www.adaptabletools.com/docs/handbook-calculated-column/index.md) |
| **Boolean** | True or False | [Alerts](https://www.adaptabletools.com/docs/handbook-alerting/index.md)                     |

AdapTable also provides 3 **specialist** Aggregation Expressions: [Cumulative](https://www.adaptabletools.com/docs/adaptable-ql-expression-cumulative/index.md), [Quantile](https://www.adaptabletools.com/docs/adaptable-ql-expression-quantile/index.md) and [Window](https://www.adaptabletools.com/docs/adaptable-ql-expression-window/index.md)

## Scalar Aggregation Expressions

Scalar Aggregation Expressions compute one value from many rows.

- By default they compute against the whole grid
- See [Grouped Aggregation](#grouped-aggregation) to limit the set of rows

They need an [Aggregation Function](https://www.adaptabletools.com/docs/adaptable-ql-expression-functions/index.md) and the [Column](https://www.adaptabletools.com/docs/dev-guide-columns-adaptable-column/index.md) being aggregated — written with **curly braces**.

The Aggregation Functions available are:

<ExpressionFunctionTable
kind="aggregated"
usedIn="aggregated"
category="aggregation"
names={[
'SUM',
'SUMIF',
'AVG',
'AVGIF',
'MIN',
'MAX',
'COUNT',
'COUNTIF',
'PRODUCT',
'PERCENTAGE',
'MEDIAN',
'MODE',
'DISTINCT',
'ONLY',
'STD_DEVIATION',
'ABSOLUTE_DIFF',
'PERCENT_DIFF',
]}
/>

- [Cumulative](https://www.adaptabletools.com/docs/adaptable-ql-expression-cumulative/index.md), [Quantile](https://www.adaptabletools.com/docs/adaptable-ql-expression-quantile/index.md) and [Window](https://www.adaptabletools.com/docs/adaptable-ql-expression-window/index.md) Functions are listed on those pages
- Developers can add [Custom Aggregation Functions](https://www.adaptabletools.com/docs/adaptable-ql-expression-functions-custom-aggregation/index.md) to supplement this list

```ts [[2, 2, "SUM"], [1, 2, "PnL"]]
// Return sum of all PnL cells
 SUM{[PnL]};
```

`AVG` can take an optional `WEIGHT` parameter to create a [Weighted Average](https://www.adaptabletools.com/docs/handbook-aggregation-weighted-average/index.md):

```ts [[2, 2, "AVG"], [1, 2, "Price"], [2, 2, "WEIGHT"], [1, 2, "index"]]
// Return the Weighted Average for the Price Column (using Index column for weighting)
 AVG{[Price], WEIGHT([index])};
```

### Aggregating a Calculation

The value being aggregated does not have to be a single Column.

It can be any Standard Expression, which is evaluated for each row and the results are then aggregated:

```ts [[2, 2, "SUM"], [1, 2, "Price"], [1, 2, "Quantity"]]
// Total traded value - sum of Price x Quantity across all rows
 SUM{[Price] * [Quantity]};
```

```ts [[2, 2, "MAX"], [2, 2, "ABS"], [1, 2, "PnL"], [2, 2, "GROUP_BY"], [1, 2, "Desk"]]
// Largest PnL move (up or down) on each Desk
 MAX{ABS([PnL]), GROUP_BY([Desk])};
```

This avoids having to create a separate Calculated Column just to hold the value being aggregated

**Example: AdapTableQL: Scalar Aggregation**

Using Scalar Aggregation Expressions in AdapTable

- This Demo contains 2 [Calculated Columns](https://www.adaptabletools.com/docs/handbook-calculated-column/index.md) that use Aggregated Scalar Expressions
  - `Popularity Ratio`
    - displays a Percentage of `Popularity` Column
    - `Popularity` is itself a Calculated Column that sums `GitHub Stars` and `GitHub Watchers`
  - `Total Stars`
    - shows a Sum of the GitHub Stars column
    - because `GROUP_BY` is not added, is the same value in each row

- Add a `GROUP_BY` to the Total Stars Expression to Sum according to Language

Demo project (Vanilla); the files without a marker are shown below:

```text
src/
  adaptableOptions.ts
  agGridModules.ts     [template]
  columnDefs.ts        [template]
  gridOptions.ts       [template]
  index.ts             [template]
  onAdaptableReady.ts  [template]
  rowData.ts           [template]
  styles.css           [template]
index.html             [template]
package.json           [template]
tsconfig.json          [template]
```

Files marked `[template]` are the same in every demo; see the [demo template](https://www.adaptabletools.com/llms/demo-template.md#vanilla).

**`src/adaptableOptions.ts`**

```ts
import {AdaptableOptions} from '@adaptabletools/adaptable';
import {WebFramework} from './rowData';

export const adaptableOptions: AdaptableOptions<WebFramework> = {
  primaryKey: 'id',
  adaptableId: 'Aggregated Scalar Expressions',
  initialState: {
    Dashboard: {
      ModuleButtons: ['CalculatedColumn'],
    },
    Theme: {CurrentTheme: 'dark'},
    Layout: {
      CurrentLayout: 'Standard Layout',
      Layouts: [
        {
          Name: 'Standard Layout',
          TableColumns: [
            'name',
            'language',
            'github_watchers',
            'github_stars',
            'githubPopularity',
            'popularityRatio',
            'totalStars',
          ],
          AutoSizeColumns: true,
        },
      ],
    },
    CalculatedColumn: {
      CalculatedColumns: [
        {
          FriendlyName: 'Popularity',
          ColumnId: 'githubPopularity',
          Expression: '[github_watchers] + [github_stars]',
          CalculatedColumnSettings: {
            DataType: 'number',
          },
        },
        {
          ColumnId: 'popularityRatio',
          FriendlyName: 'Popularity Ratio',
          Expression: 'PERCENTAGE([githubPopularity])',
          CalculatedColumnSettings: {
            DataType: 'number',
          },
        },
        {
          ColumnId: 'totalStars',
          FriendlyName: 'Total Stars',
          Expression: 'SUM{[github_stars]}',
          CalculatedColumnSettings: {
            DataType: 'number',
          },
        },
      ],
    },
  },
};
```

## Boolean Aggregation Expressions

Boolean Aggregation Expressions (see [Boolean Aggregation Expressions](#boolean-aggregation-expressions)) are the same aggregation, then compared to a threshold so the result is true / false.

- This is ideal for Limits Management and related scenarios
- For instance where you want to check that a sum comprising multiple cell values has not been exceeded

Compare with `=`, `!=`, `>`, `<`, `>=` or `<=`. Thresholds can use `K` / `M` / `B` shortcuts (in quotation marks):

```ts [[2, 2, "SUM"],[1, 2, "PnL"],[2, 2, ">"],[4, 2, "5M"]]
// Is total value of the 'PnL' column in all rows > 5 Million
 SUM{[PnL]} > '5M';
```

**Example: AdapTableQL: Boolean Aggregation**

Creating Aggregated Boolean Expressions in AdapTableQL

- This demo contains 3 [Alerts](https://www.adaptabletools.com/docs/handbook-alerting/index.md) that fire when an Aggregated Boolean Expression is triggered
  - if the lowest `Github Watchers` value is under 50
  - when the total of all the `Github Stars` rows is over 1.1 million (`'1.1M'`)
  - when the total of all `Open Issues` rows is over 9,800 (`'9.8k'`)

- In the first row change the value of:
  - `Github Watchers` column to 45 and see the `Info` Alert be fired
  - `Github Stars` column to 279429 and see the `Success` Alert be fired
  - `Open Issues` column to 1220 and see the `Warning` Alert be fired
  

Demo project (Vanilla); the files without a marker are shown below:

```text
src/
  adaptableOptions.ts
  agGridModules.ts     [template]
  columnDefs.ts        [template]
  gridOptions.ts       [template]
  index.ts             [template]
  onAdaptableReady.ts  [template]
  rowData.ts           [template]
  styles.css           [template]
index.html             [template]
package.json           [template]
tsconfig.json          [template]
```

Files marked `[template]` are the same in every demo; see the [demo template](https://www.adaptabletools.com/llms/demo-template.md#vanilla).

**`src/adaptableOptions.ts`**

```ts
import {AdaptableOptions} from '@adaptabletools/adaptable';
import {WebFramework} from './rowData';

export const adaptableOptions: AdaptableOptions<WebFramework> = {
  primaryKey: 'id',
  adaptableId: 'Aggregated Boolean Expression',
  initialState: {
    Dashboard: {
      ModuleButtons: ['Alert'],
      Tabs: [
        {
          Name: 'Toolbars',
          Toolbars: ['Alert', 'CellSummary'],
        },
      ],
    },
    StatusBar: {
      StatusBars: [
        {
          Key: 'Center Panel',
          StatusBarPanels: ['Alert'],
        },
      ],
    },
    Theme: {CurrentTheme: 'dark'},
    Alert: {
      AlertDefinitions: [
        {
          Name: 'alert-Info-44',
          MessageType: 'Info',
          Scope: {
            All: true,
          },
          Rule: 'MIN{[github_watchers]} < 50',
          AlertProperties: {
            DisplayNotification: true,
          },
        },
        {
          Name: 'alert-Success-45',
          MessageType: 'Success',
          Scope: {
            All: true,
          },
          Rule: "SUM{[github_stars]} > '1.1M'",
          AlertProperties: {
            DisplayNotification: true,
          },
        },
        {
          Name: 'alert-Warning-46',
          MessageType: 'Warning',
          Scope: {
            All: true,
          },
          Rule: "SUM{[open_issues_count]} > '9.8k'",
          AlertProperties: {
            DisplayNotification: true,
          },
        },
      ],
    },
    Layout: {
      CurrentLayout: 'Standard Layout',
      Layouts: [
        {
          TableColumns: [
            'name',
            'language',
            'github_watchers',
            'license',
            'github_stars',
            'open_issues_count',
            'created_at',
            'has_wiki',
            'updated_at',
            'pushed_at',
            'closed_issues_count',
            'open_pr_count',
            'closed_pr_count',
            'has_projects',
            'has_pages',
            'week_issue_change',
          ],
          Name: 'Standard Layout',
          AutoSizeColumns: true,
        },
      ],
    },
  },
};
```

## Grouped Aggregation

Use `GROUP_BY` to apply the Aggregation Function to a **group** of rows, rather than the entire grid.

The `GROUP_BY` clause is **always** only used together with an Aggregation Function

```ts [[2, 2, "SUM"], [1, 2, "PnL"], [2, 2, "GROUP_BY"], [1, 2, "Currency"]]
// Return Sum of all Pnl cells for rows which have this row's Currency value
 SUM{[PnL], GROUP_BY([Currency])};
```

`GROUP_BY` can receive multiple Columns:

```ts [[2, 2, "SUM"], [1, 2, "PnL"], [2, 2, "GROUP_BY"], [1, 2, "Currency"], [1, 2, "Counterparty"]]
// Return Sum of all Pnl cells for rows which have this row's Currency and Counterparty values
 SUM{[PnL], GROUP_BY([Currency], [Counterparty])};
```

### How Grouped Aggregation Works

The name `GROUP_BY` comes from a command in the SQL DB language.

However it is perhaps more illuminative to think of it as a succession of:

1. **split**: the rows are split into groups based on the value of the specified column
2. **apply**: the aggregation function is applied within each individual group
3. **combine**: the aggregation results are combined back into each row

**Example: AdapTableQL: Grouped Aggregation**

Using Scalar Aggregation Expressions with Grouping

- This Demo contains 4 [Calculated Columns](https://www.adaptabletools.com/docs/handbook-calculated-column/index.md) that use Aggregated Scalar Expressions with Grouping
  - `Watchers By Lang` - Sum of the GitHub Watchers column, grouped by Language
  - `Avg. Lang Popularity` - Average of Github Stars popularity, grouped by language
  - `Open/Closed Issues % by Lang` - Percentage of Open and Closed issues, grouped by language
  - `Lang Count` - Count of each language, also grouped language

### Expand to see the Aggregated and Grouped Scalar Expressions

The 4 Calculated Columns are defined as follows:

```
CalculatedColumn: {
  CalculatedColumns: [
    {
      ColumnId: 'total-watchers-by-lang',
      FriendlyName: 'Watchers By Lang',
      Expression: 'SUM{[github_watchers], GROUP_BY([language])}',
      CalculatedColumnSettings: {
        DataType: 'number',
      },
    },
    {
      ColumnId: 'average-popularity-by-lang',
      FriendlyName: 'Avg. Popularity by Lang',
      Expression: 'AVG{[github_stars], GROUP_BY([language])}',
      CalculatedColumnSettings: {
        DataType: 'number',
      },
    },
    {
      ColumnId: 'percentage-issues-by-lang',
      FriendlyName: 'Open/Closed Issues % by Lang',
      Expression: 'PERCENTAGE([open_issues_count], SUM{[closed_issues_count], GROUP_BY([language])})',
      CalculatedColumnSettings: {
        DataType: 'number',
      },
    },
    {
      ColumnId: 'lang-count',
      FriendlyName: 'Lang Count',
      Expression: 'COUNT{[language], GROUP_BY([language])}',
      CalculatedColumnSettings: {
        DataType: 'number',
      },
    },
]}
```

Demo project (Vanilla); the files without a marker are shown below:

```text
src/
  adaptableOptions.ts
  agGridModules.ts     [template]
  columnDefs.ts        [template]
  gridOptions.ts       [template]
  index.ts             [template]
  onAdaptableReady.ts  [template]
  rowData.ts           [template]
  styles.css           [template]
index.html             [template]
package.json           [template]
tsconfig.json          [template]
```

Files marked `[template]` are the same in every demo; see the [demo template](https://www.adaptabletools.com/llms/demo-template.md#vanilla).

**`src/adaptableOptions.ts`**

```ts
import {AdaptableOptions} from '@adaptabletools/adaptable';
import {WebFramework} from './rowData';

export const adaptableOptions: AdaptableOptions<WebFramework> = {
  primaryKey: 'id',
  adaptableId: 'Aggregated Scalar GroupBy Expressions',
  initialState: {
    Dashboard: {
      ModuleButtons: ['CalculatedColumn'],
    },
    Theme: {CurrentTheme: 'dark'},
    Layout: {
      CurrentLayout: 'Standard Layout',
      Layouts: [
        {
          Name: 'Standard Layout',
          TableColumns: [
            'name',
            'language',
            'lang-count',
            'github_watchers',
            'github_stars',
            'total-watchers-by-lang',
            'average-popularity-by-lang',
            'percentage-issues-by-lang',
          ],
          AutoSizeColumns: true,
        },
      ],
    },
    CalculatedColumn: {
      CalculatedColumns: [
        {
          ColumnId: 'total-watchers-by-lang',
          FriendlyName: 'Watchers By Lang',
          Expression: 'SUM{[github_watchers], GROUP_BY([language])}',
          CalculatedColumnSettings: {
            DataType: 'number',
          },
        },
        {
          ColumnId: 'average-popularity-by-lang',
          FriendlyName: 'Avg. Lang Popularity',
          Expression: 'AVG{[github_stars], GROUP_BY([language])}',
          CalculatedColumnSettings: {
            DataType: 'number',
          },
        },
        {
          ColumnId: 'percentage-issues-by-lang',
          FriendlyName: 'Open/Closed Issues % by Lang',
          Expression: 'PERCENTAGE([open_issues_count], SUM{[closed_issues_count], GROUP_BY([language])})',
          CalculatedColumnSettings: {
            DataType: 'number',
          },
        },
        {
          ColumnId: 'lang-count',
          FriendlyName: 'Lang Count',
          Expression: 'COUNT{[language], GROUP_BY([language])}',
          CalculatedColumnSettings: {
            DataType: 'number',
          },
        },
      ],
    },
  },
};
```

## Filtered Aggregations

AdapTableQL also provides 3 Excel-style **filtered aggregations** — `SUMIF`, `COUNTIF` and `AVGIF`.

Each takes 2 arguments:

- a Column to aggregate
- a Standard Boolean Expression (the predicate) that selects which rows contribute

```ts [[2, 2, "SUMIF"], [1, 2, "PnL"], [1, 2, "Currency"], [4, 2, "USD"]]
// Sum PnL only for rows where Currency is USD
 SUMIF{[PnL], [Currency] = "USD"};
```

```ts [[2, 2, "COUNTIF"], [1, 2, "Id"], [1, 2, "Status"], [4, 2, "Open"]]
// How many rows have Status = Open
 COUNTIF{[Id], [Status] = "Open"};
```

```ts [[2, 2, "AVGIF"], [1, 2, "Price"], [1, 2, "Region"], [4, 2, "EMEA"], [3, 2, "AND"], [1, 2, "Notional"]]
// Average Price for rows in EMEA (and positive notional)
 AVGIF{[Price], [Region] = "EMEA" AND [Notional] > 0};
```

| Function  | Meaning                                                      |
| --------- | ------------------------------------------------------------ |
| `SUMIF`   | Sum of a numeric Column over rows that match the predicate   |
| `COUNTIF` | Count of non-empty values over rows that match the predicate |
| `AVGIF`   | Arithmetic mean of a numeric Column over matching rows       |

They are functionally equivalent to putting a [`WHERE` clause](#where-clause) inside the braces of the base Aggregation:

```ts [[2, 1, "SUMIF"], [1, 1, "PnL"], [1, 1, "Currency"], [4, 1, "USD"], [2, 3, "SUM"], [1, 3, "PnL"], [5, 3, "WHERE"], [1, 3, "Currency"], [4, 3, "USD"]]
 SUMIF{[PnL], [Currency] = "USD"};
// is equivalent to:
 SUM{[PnL] WHERE [Currency] = "USD"};
```

- Unlike `SUM` / `AVG` / `COUNT`, these `*IF` forms do **not** take `GROUP_BY`, `WEIGHT` or `CUMULATIVE` clauses
- Use the base Aggregation with `WHERE` inside the braces when you need a filter together with any of these

## `WHERE` Clause

A `WHERE` clause restricts which rows are aggregated.

It is written as described in the [Guide to Writing Expressions](https://www.adaptabletools.com/docs/adaptable-ql-expression-writing/index.md) — a Standard Boolean Expression that each row must satisfy.

`WHERE` can be placed in 2 positions:

| Position                | Filters                                        | Example                                    |
| ----------------------- | ---------------------------------------------- | ------------------------------------------ |
| Inside the braces       | That single Aggregation only                   | <small>`SUM{[PnL] WHERE [Side] = 'Buy'}`</small> |
| After the Expression    | **Every** Aggregation in the Expression        | <small>`SUM{[PnL]} > '5M' WHERE [Currency] = 'USD'`</small> |

### Filtering a Single Aggregation

Put `WHERE` straight after the value being aggregated (inside the braces) to filter just that Aggregation:

```ts [[2, 2, "SUM"], [1, 2, "PnL"], [5, 2, "WHERE"], [1, 2, "Side"], [4, 2, "Buy"]]
// Total PnL of all Buy trades
 SUM{[PnL] WHERE [Side] = 'Buy'};
```

It can be combined with other clauses such as `GROUP_BY`:

```ts [[2, 2, "SUM"], [1, 2, "PnL"], [5, 2, "WHERE"], [1, 2, "Side"], [4, 2, "Buy"], [2, 2, "GROUP_BY"], [1, 2, "Currency"]]
// Total PnL of Buy trades in this row's Currency
 SUM{[PnL] WHERE [Side] = 'Buy', GROUP_BY([Currency])};
```

This makes it possible for each Aggregation in an Expression to have a **different** filter:

```ts [[2, 2, "SUM", 1], [2, 2, "SUM", 40], [1, 2, "Notional", 6], [1, 2, "Notional", 45], [5, 2, "WHERE", 16], [5, 2, "WHERE", 55], [1, 2, "Side", 23], [1, 2, "Side", 62], [4, 2, "Buy"], [2, 2, "-"], [4, 2, "Sell"]]
// Net position - Buy Notional minus Sell Notional
 SUM{[Notional] WHERE [Side] = 'Buy'} - SUM{[Notional] WHERE [Side] = 'Sell'};
```

### Filtering the Whole Expression

It is also possible to filter the entire Expression (i.e. every Aggregation), rather than a single Expression.

This is accomplished by putting the `WHERE` clause at the end of the Expression:

```ts [[2, 2, "SUM"],[1, 2, "PnL"],[2, 2, ">"],[4, 2, "5M"],[5, 2, "WHERE"],[1, 2, "Currency"],[4, 2, "USD"]]
 // Are all PnL values in rows with Dollar Currency over 5M
 SUM{[PnL]} > '5M' WHERE [Currency] = 'USD'
```

```ts [[2, 2, "SUM"], [1, 2, "PnL"], [2, 2, "COUNT"], [1, 2, "Id"], [5, 2, "WHERE"], [1, 2, "Status"], [4, 2, "Open"]]
 // Average PnL of Open trades (both SUM and COUNT only see Open rows)
 SUM{[PnL]} / COUNT{[Id]} WHERE [Status] = 'Open'
```

**Example: AdapTableQL: Boolean Aggregations with WHERE**

 Aggregated Boolean Expressions with WHERE Clauses

- This demo contains 2 [Alerts](https://www.adaptabletools.com/docs/handbook-alerting/index.md) that fire when an Aggregated Boolean Expression is triggered.
- Both are similar to the Aggregation Boolean Expressions in the demo above but with a `WHERE` clause added:
  - when the total of `Github Stars` is over 900,000 - **only** for rows where `Licence` is 'MIT License'
  - when the total of `Open Issues` is over 6,000 - **only** for rows where `Language` is 'JavaScript'

- In the first row change the value of:
  - `Github Stars` column to 279429 and see the `Success` Alert be fired (because `Licence` in first row is "MIT License")
  - `Open Issues` column to 1220 and see the `Warning` Alert be fired (because `Language` in first row is "JavaScript")
  

Demo project (Vanilla); the files without a marker are shown below:

```text
src/
  adaptableOptions.ts
  agGridModules.ts     [template]
  columnDefs.ts        [template]
  gridOptions.ts       [template]
  index.ts             [template]
  onAdaptableReady.ts  [template]
  rowData.ts           [template]
  styles.css           [template]
index.html             [template]
package.json           [template]
tsconfig.json          [template]
```

Files marked `[template]` are the same in every demo; see the [demo template](https://www.adaptabletools.com/llms/demo-template.md#vanilla).

**`src/adaptableOptions.ts`**

```ts
import {AdaptableOptions} from '@adaptabletools/adaptable';
import {WebFramework} from './rowData';

export const adaptableOptions: AdaptableOptions<WebFramework> = {
  primaryKey: 'id',
  adaptableId: 'Aggregated Boolean Expression',
  initialState: {
    Dashboard: {
      ModuleButtons: ['Alert'],
      Tabs: [
        {
          Name: 'Toolbars',
          Toolbars: ['Alert', 'CellSummary'],
        },
      ],
    },
    StatusBar: {
      StatusBars: [
        {
          Key: 'Center Panel',
          StatusBarPanels: ['Alert'],
        },
      ],
    },
    Theme: {CurrentTheme: 'dark'},
    Alert: {
      AlertDefinitions: [
        {
          Name: 'alert-Success-47',
          MessageType: 'Success',
          Scope: {
            All: true,
          },
          Rule: "SUM{[github_stars]} > '900k' WHERE [license] = 'MIT License'",
          AlertProperties: {
            DisplayNotification: true,
          },
        },
        {
          Name: 'alert-Warning-48',
          MessageType: 'Warning',
          Scope: {
            All: true,
          },
          Rule: "SUM{[open_issues_count]} > '6k' WHERE [language] = 'JavaScript'",
          AlertProperties: {
            DisplayNotification: true,
          },
        },
      ],
    },
    Layout: {
      CurrentLayout: 'Standard Layout',
      Layouts: [
        {
          TableColumns: [
            'name',
            'language',
            'github_watchers',
            'license',
            'github_stars',
            'open_issues_count',
            'created_at',
            'has_wiki',
            'updated_at',
            'pushed_at',
            'closed_issues_count',
            'open_pr_count',
            'closed_pr_count',
            'has_projects',
            'has_pages',
            'week_issue_change',
          ],
          Name: 'Standard Layout',
          AutoSizeColumns: true,
        },
      ],
    },
  },
};
```

## Combining Aggregations

Aggregation Functions return ordinary values, so they can be used anywhere in an Expression.

They can be combined together with other Aggregations, and / or with values from the current row.

### With Row Values

Mixing an Aggregation with the current row's values returns a different result for each row:

```ts [[1, 2, "Notional", 2], [2, 2, "SUM"], [1, 2, "Notional", 19], [2, 2, "GROUP_BY"], [1, 2, "Desk"], [4, 2, "100"]]
// This trade's share (%) of its Desk's total Notional
 [Notional] / SUM{[Notional], GROUP_BY([Desk])} * 100;
```

```ts [[1, 2, "Price", 2], [2, 2, "AVG"], [1, 2, "Price", 16], [2, 2, "GROUP_BY"], [1, 2, "Instrument"]]
// How far this Price is from the Instrument's average Price
 [Price] - AVG{[Price], GROUP_BY([Instrument])};
```

```ts [[1, 2, "PnL", 3], [2, 2, "AVG"], [1, 2, "PnL", 15], [2, 2, "STD_DEVIATION"], [1, 2, "PnL", 39]]
// z-score - how many standard deviations this PnL is from the average PnL
 ([PnL] - AVG{[PnL]}) / STD_DEVIATION{[PnL]};
```

In a Boolean Aggregation Expression (e.g. in an Alert) this compares each row to its group:

```ts [[1, 2, "PnL", 2], [2, 2, ">"], [2, 2, "AVG"], [1, 2, "PnL", 14], [2, 2, "GROUP_BY"], [1, 2, "Desk"], [4, 2, "3"]]
// Rows whose PnL is more than 3 times their Desk's average PnL
 [PnL] > AVG{[PnL], GROUP_BY([Desk])} * 3;
```

### With Other Aggregations

Aggregations can be combined with arithmetic:

```ts [[2, 2, "SUM", 1], [1, 2, "PnL"], [2, 2, "SUM", 14], [1, 2, "Notional"], [4, 2, "100"]]
// Return on Notional (%) across the whole Grid
 SUM{[PnL]} / SUM{[Notional]} * 100;
```

Boolean Aggregation Expressions can join several conditions with `AND` / `OR`:

```ts [[2, 2, "SUM"], [1, 2, "Notional"], [4, 2, "10M"], [3, 2, "AND"], [2, 2, "COUNT"], [1, 2, "Id"], [4, 2, "50"]]
// Alert when total Notional exceeds 10M AND there are more than 50 trades
 SUM{[Notional]} > '10M' AND COUNT{[Id]} > 50;
```

```ts [[2, 2, "MAX"], [1, 2, "Exposure"], [4, 2, "2M"], [3, 2, "OR"], [2, 2, "SUM"], [1, 2, "PnL"], [4, 2, "-500K"]]
// Alert when any single Exposure is over 2M OR total PnL falls below -500K
 MAX{[Exposure]} > '2M' OR SUM{[PnL]} < '-500K';
```

The demo at the top of this page combines Aggregations with row values, `WHERE`, `SUMIF` and `COUNTIF`

### FAQ

**When should I use `SUMIF` instead of `WHERE`?**
`SUMIF{[PnL], [Currency] = "USD"}` and `SUM{[PnL] WHERE [Currency] = "USD"}` give the same result - use whichever reads better. 
Use `WHERE` inside the braces if you also need `GROUP_BY`, `WEIGHT` or `CUMULATIVE`, and `WHERE` at the end of the Expression when one filter should apply to every Aggregation in it.

**Is there a `SUMIFS` or `AVERAGEIF`?**
No. AdapTableQL ships `SUMIF`, `COUNTIF` and `AVGIF` only. 
Combine conditions with `AND` / `OR` in the predicate (e.g. `SUMIF{[PnL], [Currency] = "USD" AND [Region] = "EMEA"}`). 
Excel’s `AVERAGEIF` maps to `AVGIF`.

**Does Aggregation take Pagination into account?**
No. **All** the data in the AG Grid data source is used when aggregating and not solely what is on the current page.
