Aggregation

Compute totals, averages, and group-bys directly against your dataset. No need to fetch every row to your client just to add up a column.

What it does

GET /api/v1/datasets/:public_id/records/aggregate accepts metric, group_by, and the same filter[col][op]=val syntax as the list endpoint. Up to 5 metrics per request, up to 3 group-by columns. Aggregations run server-side with a hard statement timeout, and column names are validated against your dataset's schema before any SQL touches the database. The response is a JSON array of grouped result rows, capped at 10,000 groups per request.

How it works

1. 1

   Pick a metric

   metric=count, metric=sum:revenue, metric=avg:age, metric=min:created_at — comma-separate up to 5 in one call.

2. 2

   Optionally group by columns

   Add group_by=city,plan to slice your metric across one or more dimensions.

3. 3

   Filter the input set

   Use the same filter[col][op]=val syntax as the list endpoint to scope the aggregation. Filters and metrics compose freely.

See it in action

# Average age and row count, grouped by city
curl -G "https://csv-api.com/api/v1/datasets/d_a8f3bc91/records/aggregate" \
  -H "Authorization: Bearer YOUR_KEY" \
  --data-urlencode "metric=count,avg:age" \
  --data-urlencode "group_by=city" \
  --data-urlencode "filter[plan]=pro"

# → 200 OK
{
  "data": [
    { "city": "Portland", "count": 12, "avg_age": 31.5 },
    { "city": "Seattle",  "count":  8, "avg_age": 29.0 },
    { "city": "Denver",   "count":  5, "avg_age": 34.4 }
  ],
  "meta": { "metrics": ["count", "avg_age"], "group_by": ["city"], "row_count": 3 }
}

Why it matters

• Less data over the wire

  Move math to the server. A dashboard that needs 'total revenue by month' is one request, not a million-row download.

• Whitelisted and safe

  Only count, sum, avg, min, max are allowed, and column names are validated against the dataset before any SQL is built.

• Bounded and timed

  Statement timeout, max 5 metrics, max 3 group-by columns, hard 10,000-group cap. A bad query can't take the database down.

Common use cases

• Dashboards that need totals and group-bys without shipping every row to the client

• Reports: 'how many leads per source per week', 'average order value by region'

• Embedded charts in static sites or notebook prototypes

• Sanity checks during data import — count rows by status before and after