Setting the file. One moment.
Subchapter 2.1
references/aggregation.mdMarkdown3 KBView on GitHub
Server-side aggregation using ?aggregate and ?group_by parameters. Produces flat summary rows instead of raw data.
| Function | Description | Example |
|---|---|---|
count(*) | Count all rows | aggregate=count(*) |
count(col) | Count non-null values | aggregate=count(score) |
count_distinct(col) | Count distinct values | aggregate=count_distinct(state) |
sum(col) | Sum values | aggregate=sum(enrollment) |
avg(col) | Average | aggregate=avg(score) |
min(col) | Minimum | aggregate=min(year) |
max(col) | Maximum | aggregate=max(score) |
Aggregate results use auto-generated aliases:
| Expression | Output column |
|---|---|
count(*) | count |
avg(score) | avg_score |
sum(enrollment) | sum_enrollment |
count_distinct(state) | count_distinct_state |
Total row count (no group_by):
curl 'https://api.tryopendata.ai/v1/datasets/nces/naep?aggregate=count(*)'
# Returns: [{"count": 2288}]Average score by year:
curl 'https://api.tryopendata.ai/v1/datasets/nces/naep?group_by=year&aggregate=avg(score)&sort=year'
# Returns: [{"year": 2003, "avg_score": 234.5}, {"year": 2005, "avg_score": 237.1}, ...]Multiple aggregates:
curl 'https://api.tryopendata.ai/v1/datasets/nces/naep?group_by=year&aggregate=avg(score),min(score),max(score),count(*)&sort=-year'Filtered aggregation:
curl 'https://api.tryopendata.ai/v1/datasets/nces/naep?group_by=year&aggregate=avg(score)&filter%5Bjurisdiction_name%5D=Illinois&sort=year'Multi-column grouping:
curl 'https://api.tryopendata.ai/v1/datasets/nces/naep?group_by=year,subject&aggregate=avg(score)&sort=year'When aggregate is provided:
group_by columns (if any)sort paramlimit/offset pagination applies to the grouped resultsWithout group_by, the entire (filtered) dataset collapses to a single summary row.
aggregate and nest_fields/nest_field are mutually exclusive. Nesting produces hierarchical grouping with raw rows; aggregation produces flat summary rows. Combining them returns a 400 error.
Column names must exist in the schema. Referencing a nonexistent column returns a 400 error listing available columns.
Only allowlisted functions. Anything outside count, sum, avg, min, max, count_distinct is rejected. This is a security measure against SQL injection.
count(*) is the only function that accepts *. Using avg(*) or sum(*) returns a 400 error.
You can sort results by aggregated column names. Use the auto-generated alias as the sort field:
curl '.../datasets/provider/dataset?aggregate=count(event_id)&group_by=year&sort=-count_event_id'Invalid sort fields return a 400 error with a valid_values array showing available column names.