Skip to content

Aggregate: totals, averages and time buckets

A time series can answer with groups instead of rows: hourly traffic per counter, a day's low, high and mean temperature, how many stations reported. Ask with the standard OData $apply expression — the system query option of the OASIS OData Extension for Data Aggregation Version 4.0 — in the body of the same service query you already send. It is computed where the data is, so a day of ten-minute counts from every counter (about 17,000 rows) comes back as the hundred or so totals you wanted, in well under a second.

POST https://quicklookup.com/api/v1/services/{serviceGid}/query
Authorization: Bearer <your API key>
Content-Type: application/json

{
  "inputs": {"from": "2026-09-24T00:00:00Z", "to": "2026-09-25T00:00:00Z"},
  "$apply": "groupby((traffic_station_id,traffic_direction_id),aggregate(vehicle_count with sum as vehicles))"
}

{
  "items": [
    {"traffic_station_id": "60", "traffic_direction_id": "0", "vehicles": 1452.0},
    {"traffic_station_id": "60", "traffic_direction_id": "1", "vehicles": 1388.0}
  ]
}

Each item is one group: its grouping properties and each aggregate under the alias you gave it — the result the specification defines. The rest of the answer (serviceGid, provenance, coverage, source) is the envelope every query carries.

What you can write

$apply is a sequence of transformations separated by /. QuickLookup supports the ones a time series needs, in this order:

Transformation Use
filter(…) Keep rows first. Comparisons eq, gt, ge, lt, le and in (…), joined with and, of a property or of a time part of timestamp: filter(traffic_station_id eq '60' and vehicle_count ge 1), filter(hour(timestamp) lt 13), filter(date(timestamp) in (2026-09-01,2026-09-08))
compute(… as alias) A time part to group by: date, year, month, day, hour or minute of timestamp (UTC)
groupby((…), aggregate(…)) Group by dimensions, computed time parts or timestamp, and aggregate each group. Without aggregate, the distinct groups themselves. Last.
aggregate(…) Aggregate everything into one item. Last.

Aggregation methods: sum, min, max, average and countdistinct (metric with sum as alias), and $count as alias for the number of readings. countdistinct also applies to a dimension (traffic_station_id with countdistinct as stations); the others read metrics. timestamp with min and timestamp with max say when a group's readings begin and end.

The property names are the service's own: its metrics and dimensions, exactly as inspecting the service lists them.

Examples

Hourly traffic per counter — one call for the whole day, ready to draw:

compute(hour(timestamp) as hour)
  /groupby((traffic_station_id,traffic_direction_id,hour),aggregate(vehicle_count with sum as vehicles))

A day's low, high and mean — one item, where you would otherwise make three calls:

aggregate(temperature with min as low,temperature with max as high,temperature with average as mean)

Daily totals over a month, one counter:

filter(traffic_station_id eq '60')
  /compute(date(timestamp) as day)
  /groupby((day),aggregate(vehicle_count with sum as vehicles))

How complete is a day — readings per counter, and the last one each has:

groupby((traffic_station_id),aggregate($count as readings,timestamp with max as last))

What is usual by now — the same hours on the same weekday of earlier weeks, summed per week, to set beside today's running total:

filter(hour(timestamp) lt 14 and date(timestamp) in (2026-09-04,2026-09-11,2026-09-18))
  /compute(date(timestamp) as day)
  /groupby((day),aggregate(vehicle_count with sum as vehicles))

Which counters exist — the distinct groups, no aggregate:

groupby((traffic_station_id,traffic_direction_id))

Window, order and size

The inputs still bound the window, as for every query: aggregation groups what is in the window, it never widens it. order sorts by the grouping properties (asc by default, desc reverses), and limit caps the number of groups — 10,000 unless you set it lower. An answer with more groups than limit is refused rather than cut short, because a silently truncated total is worse than none: group coarser or narrow the window. An aggregated answer has no nextCursor.

A source QuickLookup fetches on demand is fetched exactly as for a page of rows before it is aggregated — and with Prefer: respond-async you are answered 202 while it is being fetched, then the groups on the next ask.

An aggregate is only ever computed over the whole window. While part of it has never been fetched, the answer is pending with no groups — not the groups of what happens to be stored, because a total over half a week looks like a total and is not. Once every part has been fetched, older data being refreshed does not hold the answer back.

What is refused

Anything outside the list above is refused with 422 and a message in fields.$apply naming what was not supported — ne and or in filter, other transformations (topcount, concat, …), other functions in compute, grouping by a metric, or an alias that is also a property name. Event feeds and lookups do not aggregate; $apply is offered for time series.

From an agent

The MCP tool query_data_service takes the same $apply argument and returns the same groups in items.