Skip to content
excalibase
aggregations
REST APIstable

Aggregations

On Excalibase Cloud

The REST API of a cloud project returns rows; it has no aggregate functions in ?select=. Aggregates are a GraphQL feature: every table has a ...Aggregate field with count, sum, avg, min and max, narrowed by the same where as the list field. A role may aggregate a table only when its permission sets allowAggregations; otherwise the field is not in its schema.

Set BASE, PROJECT_ID and TOKEN as in Connect to your project.

{
  publicLivePollVotesAggregate {
    count
    sum { id }
    avg { id }
    min { id }
    max { id }
  }
}
{
  publicLivePollVotesAggregate(where: { poll_id: { isNotNull: true } }) {
    count
  }
}

Send either one as the query of a POST:

curl -s -X POST "$BASE/$PROJECT_ID/graphql" \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"query":"{ publicLivePollVotesAggregate { count sum { id } } }"}'
{ "data": { "publicLivePollVotesAggregate": { "count": 2, "sum": { "id": "7" } } } }

A row total for a REST list needs the same permission: Prefer: count=exact on a REST request answers 403 permission_denied for a role that may not count. See Filtering.

Self-hosting

This section is for the standalone excalibase-rest service. It accepts aggregate functions inline in ?select=. This is self-hosted only: a cloud project answers 400 Unknown column for these.

Use ?select= with aggregate functions for instant analytics — no separate endpoints needed.

Functions

FunctionExample
count()?select=count()
sum(col)?select=amount.sum()
avg(col)?select=amount.avg()
min(col)?select=amount.min()
max(col)?select=amount.max()

Examples

Count all records

GET /api/v1/payment?select=count()
{ "data": [{ "count": 14596 }] }

Sum payments by customer

GET /api/v1/payment?select=customer_id,amount.sum()
{
  "data": [
    { "customer_id": 1, "sum": 118.68 },
    { "customer_id": 2, "sum": 128.73 }
  ]
}

Multiple aggregates

GET /api/v1/payment?select=amount.sum(),amount.avg(),amount.max(),count()
{
  "data": [{
    "sum": 67416.51,
    "avg": 4.20,
    "max": 11.99,
    "count": 16049
  }]
}

Group by with filter

GET /api/v1/rental?select=staff_id,count()&return_date=not.is.null
{
  "data": [
    { "staff_id": 1, "count": 8040 },
    { "staff_id": 2, "count": 7290 }
  ]
}

Top spenders

GET /api/v1/payment?select=customer_id,amount.sum()&orderBy=sum&orderDirection=desc&limit=10