Morel Cookbook

Problem

I want counts and totals per group — per region, per customer, per product — with one or more keys. Same shape as SQL GROUP BY with aggregates, but as a composable step.

Setup

val orders = [
  { id = 1, productId = 101, quantity = 12, region = "north" },
  { id = 2, productId = 204, quantity = 30, region = "south" },
  { id = 3, productId = 103, quantity = 8,  region = "north" },
  { id = 4, productId = 204, quantity = 50, region = "west"  },
  { id = 5, productId = 101, quantity = 6,  region = "east"  },
  { id = 6, productId = 202, quantity = 20, region = "south" },
  { id = 7, productId = 103, quantity = 14, region = "west"  },
  { id = 8, productId = 102, quantity = 25, region = "west"  }
];

Example

Order count and total quantity per region:

from ord in orders
  group ord.region
    compute { orderCount = count over (), totalQty = sum over ord.quantity };

val it =
  [{orderCount=2,region="north",totalQty=20},
   {orderCount=2,region="south",totalQty=50},
   {orderCount=3,region="west",totalQty=89},
   {orderCount=1,region="east",totalQty=6}]
  : {orderCount:int, region:string, totalQty:int} list

What's happening

group <key> compute <aggregates> is one step, not two. The key expression produces the grouping value — here ord.region, which becomes a field called region in the output. The compute clause lists aggregates, each written as `name = f over

`. `count over ()` is the row counter; the others take a field, as in `sum over ord.quantity`.

The output is a new record type, built from the group key's field and each named aggregate. Drop compute and you get the distinct key values as a list — that's SELECT DISTINCT. Group by a record (group { ord.region, ord.productId }) for multiple keys.

On naming aggregates: 0.9 names them for you. Write compute { count over (), sum over ord.quantity } and you get fields count and sum. The name comes from the aggregate function, so two of the same kind collide — { sum over a, sum over b } fails with "duplicate field 'sum' in record", and you go back to explicit name = … bindings.

Naming the key is the trap. group month = e is not SQL's AS month: it binds month as a variable for later steps, while the field name is still derived from e. When e is a call like f x, no name can be derived and you get "cannot derive label for group expression". To name the column, use braces: group { month = e }.

Another gotcha: the basis has count, sum, min, max and only, but no avg. Compute the mean in a follow-up yield — see below. The same shape gives you a weighted mean or a ratio.

Variations

Distinct regions — no aggregates, just the key:

from ord in orders
  group ord.region;

Mean quantity per region, in a follow-on yield. Note the real conversions — integer division would truncate.

from ord in orders
  group ord.region
    compute { n = count over (), total = sum over ord.quantity }
  yield { region, mean = real total / real n };

See also