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
| Function | Example |
|---|---|
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