7.10.14. SQL-13 — Aggregates: sum/avg/…
Source shape |
Emitted SQL |
Result type |
|---|---|---|
|
|
|
|
|
column type |
|
|
|
|
|
column type |
|
|
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.10.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.10.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.10.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.10.14.4. Aggregating a computed expression
_select accepts a computed scalar, not just a bare column.
The expression lowers into the aggregate’s argument:
let s = _sql(db |> select_from(type<Car>) |> _select(_.Price + _.Id) |> sum())
// SELECT SUM(("Price") + ("Id")) FROM "Cars"
Workhorse casts inside the projection lower to a SQLite CAST and
drive the read-back type. Wrapping in int64(...) makes the
projection 64-bit, so the SUM reads back faithfully instead of
truncating at 32-bit scale:
let s = _sql(db |> select_from(type<Car>)
|> _select(int64(_.Price) * int64(_.Id))
|> sum())
// SELECT SUM((CAST("Price" AS INTEGER)) * (CAST("Id" AS INTEGER))) FROM "Cars"
7.10.14.5. 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.10.14.6. 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