7.9.15. SQL-14 — `_group_by` and `_having`

_group_by(key) partitions rows into buckets. After grouping, the chain element is a (key, group-rows) tuple shaped like C# LINQ’s IGrouping<K, T>:

Group-tuple shape	Meaning
`_._0`	group key (single-key form)
`_._0._N`	N-th key column (multi-key form)
`_._1`	array of rows in this bucket

7.9.15.1. Aggregating over the group

The macro recognizes a fixed set of pipe shapes on _._1:

Source shape	Emitted SQL
`_._1 \|> length`	`COUNT(*)`
`_._1 \|> count`	`COUNT(*)` (alias)
`_._1 \|> select($(u : T) => u.Field) \|> sum`	`SUM("Field")`
`_._1 \|> select($(u : T) => u.Field) \|> average`	`AVG("Field")`
`_._1 \|> select($(u : T) => u.Field) \|> min`	`MIN("Field")`
`_._1 \|> select($(u : T) => u.Field) \|> max`	`MAX("Field")`

The inner select lambda parameter is intentionally named ($(u : User)), not _. The outer _select lambda already binds _ to the group tuple, and daslang flags a same-name- different-type lambda nested inside as a shadowing error. Naming the inner parameter sidesteps the conflict; the macro recognizes the body as <param>.<field> for any parameter name.

7.9.15.2. Single-key grouping

let by_city <- _sql(db |> select_from(type<User>)
                      |> _group_by(_.City)
                      |> _order_by(_._0)
                      |> _select((City = _._0, N = _._1 |> length)))
// SELECT "City", COUNT(*) FROM "Users" GROUP BY "City"

7.9.15.3. Multiple aggregates in one projection

let stats <- _sql(db |> select_from(type<User>)
                    |> _group_by(_.City)
                    |> _select((
                        City   = _._0,
                        N      = _._1 |> length,
                        MinAge = _._1 |> select($(u : User) => u.Age)    |> min,
                        MaxAge = _._1 |> select($(u : User) => u.Age)    |> max,
                        Total  = _._1 |> select($(u : User) => u.Salary) |> sum)))

7.9.15.4. Pre-aggregate filter — `_where`

_where before _group_by filters input rows — it lowers to WHERE (pre-aggregate). The macro routes binds correctly: WHERE binds first, then HAVING, then LIMIT.

_sql(db |> select_from(type<User>)
       |> _where(_.Age >= 30)
       |> _group_by(_.City)
       |> _select((City = _._0, N = _._1 |> length)))
// ... WHERE "Age" >= ? GROUP BY "City"

7.9.15.5. Post-aggregate filter — `_having`

_having(predicate) filters on the aggregated buckets. It sits between _group_by and the projection. Aggregate expressions inside _having translate the same way as inside _select:

_sql(db |> select_from(type<User>)
       |> _group_by(_.City)
       |> _having(_._1 |> length > 1)
       |> _select((City = _._0, N = _._1 |> length)))
// SELECT "City", COUNT(*) FROM "Users"
// GROUP BY "City" HAVING COUNT(*) > ?

7.9.15.6. Multi-key grouping

Pass a tuple to _group_by. Each tuple field becomes its own GROUP BY column. Project the keys with _._0._N:

_sql(db |> select_from(type<User>)
       |> _group_by((_.City, _.Age))
       |> _select((City = _._0._0, Age = _._0._1, N = _._1 |> length)))
// SELECT "City", "Age", COUNT(*) FROM "Users"
// GROUP BY "City", "Age"

7.9.15.7. The full reporting query

The chain mirrors SQL clause order one-for-one:

let report <- _sql(db |> select_from(type<User>)
                     |> _where(_.Age >= min_age)
                     |> _group_by(_.City)
                     |> _having(_._1 |> length >= min_count)
                     |> _order_by(_._0)
                     |> _select((
                         City      = _._0,
                         N         = _._1 |> length,
                         AvgSalary = _._1 |> select($(u : User) => u.Salary) |> average))
                     |> take(10))

// SELECT "City", COUNT(*), AVG("Salary") FROM "Users"
// WHERE "Age" >= ? GROUP BY "City" HAVING COUNT(*) >= ?
// ORDER BY "City" ASC LIMIT ?

7.9.15. SQL-14 — _group_by and _having