7.9.14. SQL-13 — Aggregates: sum/avg/…

Source shape	Emitted SQL	Result type
... |> count()	SELECT COUNT(*) ...	int
... |> _select(_.Col) |> sum()	SELECT SUM("Col") ...	column type
... |> _select(_.Col) |> average()	SELECT AVG("Col") ...	double
... |> _select(_.Col) |> min()	SELECT MIN("Col") ...	column type
... |> _select(_.Col) |> max()	SELECT MAX("Col") ...	column type

All five are terminals — they end the chain and produce a scalar, not an array. The four column-driven aggregates need a _select(_.Col) to pick the column; count operates on rows, not values, and stands alone.

7.9.14.1. count — whole-source row count

let n = _sql(db |> select_from(type<Car>) |> count())
// SELECT COUNT(*) FROM "Cars"

count returns int — matching daslang’s length(arr) return type — not int64. SQLite’s underlying COUNT returns int64; the column-reader narrows to int.

7.9.14.2. sum / min / max — inherit column type

let total    = _sql(db |> select_from(type<Car>) |> _select(_.Price) |> sum())
let cheapest = _sql(db |> select_from(type<Car>) |> _select(_.Price) |> min())
let priciest = _sql(db |> select_from(type<Car>) |> _select(_.Price) |> max())

The result element type matches the projected column. Price is int, so total is int; Price of type int64 would produce int64.

7.9.14.3. average — always promotes to double

let mean = _sql(db |> select_from(type<Car>) |> _select(_.Price) |> average())
// SELECT AVG("Price") FROM "Cars"     -- result type: double

7.9.14.4. Composing with _where

A _where predicate filters the input rows the aggregate sees:

let cutoff = 100
let total_over_100 = _sql(db |> select_from(type<Car>)
                            |> _where(_.Price > cutoff)
                            |> _select(_.Price)
                            |> sum())
// SELECT SUM("Price") FROM "Cars" WHERE "Price" > ?

7.9.14.5. Per-bucket aggregates — _group_by

Aggregates inside _group_by are covered in SQL-14 — _group_by and _having. There the same primitives (length / sum / average / min / max) appear inside an inner select lambda over the group elements.

See also

Full source: tutorials/sql/13-aggregates.das

Previous tutorial: SQL-12b — Set operations

Next tutorial: SQL-14 — _group_by and _having