Aggregating

A read can return aggregates instead of rows: totals, counts, averages, minimums and maximums, optionally per group. PostgreSQL computes them, under the same permissions and row filters as any read.

Aggregates

_<function>[<field>]=<output name>

The output name is required. Fields can be relation paths. Aggregating a field needs READ on it.

Results keep PostgreSQL's types, and the JSON follows them: _count is a bigint, and a _sum or _avg of a numeric column is numeric. Both arrive as strings ("31", "1250.50"), so no precision is lost on large or decimal values. Convert them in your client. _min and _max keep the column's own type.

How many open orders, and when the oldest was reported:

GET /api/public/table/work_orders?where[status][_nin]=COMPLETED,CANCELLED&_count[*]=open&_min[reported_at]=oldest

{ "data": [ { "open": "31", "oldest": "2026-09-28T14:10:00Z" } ] }

Groups

group_by splits the rows into groups and computes the aggregates per group:

GET /api/public/table/work_orders
    ?group_by=branch.name,status
    &_count[*]=orders
    &where[reported_at][_gte]=$PERIOD.this_month.from
    &order_by=branch.name,-orders

{ "data": [
  { "branch.name": "Jujuy", "status": "ASSIGNED", "orders": "6" },
  { "branch.name": "Jujuy", "status": "NEW", "orders": "3" },
  { "branch.name": "Salta", "status": "IN_PROGRESS", "orders": "11" }
] }

• A group field's output key is its path ("branch.name").
• With group_by or an aggregate, each result holds only the group fields and the aggregates. fields is ignored.
• order_by can use group fields and aggregate names.
• count=true counts groups, not rows.

Filtering groups: having

where filters rows before grouping. having filters groups after, by their aggregates, with the same syntax:

GET /api/public/table/work_order_parts
    ?group_by=part.sku,part.name
    &_sum[quantity]=used
    &where[work_order.reported_at][_gte]=$PERIOD.this_quarter.from
    &having[used][_gt]=50

That reads "parts used more than 50 times this quarter". where can't refer to aggregate names; having can refer to both group fields and aggregates.

When to use a view instead

Aggregates over the Data API cover counts and sums of columns. For anything that needs an expression, such as quantity * unit_price, a period bucket like date_trunc('month', issued_on), or a window function, write a view and read it:

CREATE VIEW invoices_by_month AS
SELECT date_trunc('month', i.issued_on)::date AS month,
       sum(it.quantity * it.unit_price) AS total
FROM invoices i JOIN invoice_items it ON it.invoice = i.id
GROUP BY 1;

For reports and charts, Dashboards run SQL directly.

The SQL it runs

This is the SQL TotalBase sent to PostgreSQL for the example above, as PostgreSQL's own log recorded it - ids shortened, long column lists cut with …. Every value travels as a parameter ($1), never inside the SQL text. The groups example above:

SELECT t1."name" AS "branch.name", t0."status" AS "status", COUNT(*) AS "orders"
FROM "public"."work_orders" AS t0
LEFT JOIN "public"."branches" AS t1 ON t1."id" = t0."branch"
WHERE t0."reported_at" >= date_trunc('month', CURRENT_TIMESTAMP)   -- $PERIOD.this_month.from
GROUP BY t1."name", t0."status"
ORDER BY t1."name" ASC, "orders" DESC
LIMIT $1 OFFSET $2;

A caller with a row filter gets it in the WHERE, before grouping: aggregates only ever count the rows they may read.

Next steps

• Writing.
• Views.