Skip to main content

Aggregate records

An aggregation turns matching records into compact summary rows. Use it to count records, compare groups, or calculate numeric values without transferring every source row to the application.

The SDKs use aggregate(). REST uses _aggregate on the ordinary table data route.

Use the aggregate forms listed on this page: count(*) or function(field). Compose business calculations from returned aggregate values in application code.

Aggregate reads return a summary result set rather than a page of source records. An ungrouped aggregate returns one summary row; a grouped aggregate returns all matching groups and reports the number of returned groups as total. Use root-table filters and low-cardinality group fields to keep that result bounded.

Count every record​

count(*) returns one summary row and uses the alias count.

await database
  .from('tasks')
  .aggregate('count(*)')
  .first();

Group records​

Group by done to produce one row for completed tasks and another for incomplete tasks.

await database
  .from('tasks')
  .aggregate('count(*)')
  .groupBy('done')
  .sort('count', 'desc')
  .sort('done', 'asc')
  .execute();

A representative data value is:

[
  {"done": false, "count": 12},
  {"done": true, "count": 8}
]

Pass several fields to the SDK method, or use a comma-separated REST value to group by more than one field.

Calculate several summaries​

One request can include several aggregate expressions. This example groups projects by status and calculates the count, average, minimum, and maximum priority.

await database
  .from('projects')
  .aggregate(
    'count(*)',
    'avg(priority)',
    'min(priority)',
    'max(priority)',
  )
  .groupBy('status')
  .sort('status', 'asc')
  .execute();

Filter source records​

Ordinary filters on the root table run before aggregation. This example counts only incomplete tasks.

await database
  .from('tasks')
  .filters('done', 'eq', false)
  .aggregate('count(*)')
  .execute();

Keep an aggregate request focused on one table. Query related tables separately when a summary depends on them. Vector and hybrid search are also separate query modes; keep those queries separate from aggregation.

Filter grouped results​

Use a having condition after grouping. Conditions refer to generated aggregate aliases; count__gte=10 keeps groups with at least ten records.

await database
  .from('tasks')
  .aggregate('count(*)')
  .groupBy('done')
  .having('count__gte=10')
  .sort('count', 'desc')
  .sort('done', 'asc')
  .execute();

Aggregate functions and aliases​

ExpressionDefault aliasResult
count(*)countNumber of matching records
count(field)count_fieldNumber of non-null values
sum(field)sum_fieldSum of numeric values
avg(field)avg_fieldAverage of numeric values
min(field)min_fieldSmallest value
max(field)max_fieldLargest value
array_agg(field)array_agg_fieldValues collected in an array
json_agg(field)json_agg_fieldValues collected as JSON
stddev(field)stddev_fieldStandard deviation
variance(field)variance_fieldVariance

Sort fields can reference group fields or aggregate aliases. A having condition can reference only an aggregate alias, such as count or sum_amount. Use root-table filters and carefully chosen group fields when a query can produce many summary rows.

Next steps​