Grouping and aggregation

Grouping is how you go from "every row" to "one row per category". It works the same way as SQL's GROUP BY, but in AQL it's its own pipe-able step.

Try it interactively

Practice in the AQL Playground: Aggregate Functions · Dimensions & Measures · Function: select & group.

group() on its own

group() takes a table and returns one row per distinct value of the dimension you pass:

order_items | group(order_items.product_id)

By itself that's just a list of unique product IDs. Useful, but the real point is to combine it with aggregations.

Adding an aggregation

Pipe the grouped table into an aggregator like count, sum, or avg. You get one number per group:

order_items
| group(order_items.product_id)
| select(order_items.product_id, count(order_items.id))

That's "number of items sold, per product".

Grouping across models

Because AQL knows your relationships, you can group by a column on a related model without joining:

order_items
| group(products.category_name)
| select(products.category_name, sum(order_items.revenue))

A subtle thing about group()

Inside a group(), when you reference rows from other models, you get all rows related to the current group, not just one. That's what makes the aggregation work: count(order_items.id) inside a group sees every order item for that product, not just one.

This is the same trick that powers nested aggregation later. For now, just know: after group(), references to related rows fan out into collections that aggregators can collapse.

The select() must match

Whatever you select() after a group() has to either be the grouping dimension or an aggregation. This fails:

order_items
| group(order_items.product_id)
| select(order_items.product_id, countries.name, count(order_items.id))
//                                ^^^^^^^^^^^^^^ not grouped, not aggregated

Same rule as SQL's "column must appear in GROUP BY or be aggregated".

Next

→ Cross-model queries: how AQL reaches into related models without you writing joins.