records_aggregate()

Calculate a count, sum, average, minimum or maximum over the records that match a set of filters. Add a group field to get one value per group (for example, deal value per stage or per month).

Syntax

records_aggregate(table, action?, field?, filters?, options?)

Parameters

Name	Type	Required	Description
`table`	string or number	yes	The table's name, its database table name, or its numeric table id, exactly as for records_query().
`action`	string	no	`count` (default), `sum`, `avg`, `min` or `max`.
`field`	string	for all but `count`	The field to calculate on: its id or its label (labels ignore case, and spaces may be written as underscores). Ignored for `count`. `sum` and `avg` need a number, calculation or rollup field. `min` and `max` work on any single-value field (not files, long text, multiple select and similar).
`filters`	array	no	Filter conditions in the same format as records_query(), with the same operators and the same matching rules. All conditions must match. Omit to include every record.
`options`	array	no	Grouping and sorting, see below.

Options

Key	Description
`group_by`	Field to group by (id or label). Text, number, date, single select, lookup, user and calculation fields can be grouped.
`group_date`	`day`, `week`, `month` or `year`. Only for a date group field. Labels look like `2026-01-05`, `2026-W02` (ISO week), `2026-01` and `2026`.
`sort`	`value_desc` (default, largest first), `value_asc`, `label_asc` or `label_desc`.
`limit`	Maximum number of groups, 1 to 200 (default 200).

Returns

Without group_by: a number. count returns an integer (0 when nothing matches). sum, avg, min and max return null when no records match.
With group_by: an associative array of group => value, in the chosen sort order, for example ["Won" => 150, "New" => 30]. Read one group directly with $totals["Won"], or loop with foreach ($totals as $group => $value). A lookup group is named by the linked record's title (two records with the same title are merged into one group). A user group is named by the user's email. Records with an empty group value are collected under the key "".

Examples

Total value of won deals:

$won = records_aggregate("deals", "sum", "amount", [
    ["field" => "stage", "op" => "=", "value" => "Won"]
]);

Deal value per month, oldest first:

$byMonth = records_aggregate("deals", "sum", "amount", [], [
    "group_by" => "closed", "group_date" => "month", "sort" => "label_asc"
]);
foreach ( $byMonth as $month => $total ) {
    sys_log($month . ": " . $total);
}

Open tickets per assignee:

$load = records_aggregate("tickets", "count", null, [
    ["field" => "status", "op" => "!=", "value" => "closed"]
], ["group_by" => "assignee"]);

Example output

[
    "2026-01" => 18250,
    "2026-02" => 22100
]

Notes

Group names that look like whole numbers (a year such as 2026, or a number field) become integer array keys. $totals[2026] and $totals["2026"] both work, but use == rather than === when comparing a key to a string.
Grouped results are capped at 200 groups. When a result fills the limit, the run log shows a warning that some groups may have been left out.
Filters behave exactly as in records_query(), so a total always agrees with the records that query would return.
In the visual builder, the Aggregate Records step does the same thing without code.