Tutorial: Building with CleaveDB

Grouping with GROUP BY

When analyzing data partitioned across categories (such as departments, roles, regions, or statuses), GROUP BY segments documents by a designated attribute and computes aggregates independently for each partition.

Basic GROUP BY aggregation

Specify the grouping field with GROUP BY <field> before your aggregation expressions:

DISTILL FROM staff GROUP BY dept TOTAL age AS total_age

Unlike scalar aggregations that return a single JSON object, GROUP BY queries return an array of objects—one for each distinct grouping value:

[
  { "dept": "Eng", "total_age": 63.0 },
  { "dept": "Ops", "total_age": 56.0 }
]

Grouping with counts and averages

Count members per department or compute average metrics:

DISTILL FROM staff GROUP BY dept COUNT AS n

Or compute average values across groups:

DISTILL FROM staff GROUP BY dept AVERAGE age

Multi-metric group aggregations

Calculate multiple statistics simultaneously within each group:

DISTILL FROM staff GROUP BY role TOTAL age AS total, AVERAGE age AS avg

This yields entries containing the grouping key, the total sum, and the average per role in one scan.

Combining GROUP BY with WHERE filters

Filter records before grouping to omit unwanted rows from group totals:

DISTILL FROM staff GROUP BY dept WHERE age > 25 COUNT AS n

You can also place the WHERE clause at the end of the query:

DISTILL FROM staff GROUP BY dept TOTAL age AS t WHERE status = "active"