8.11.14. SQL-13 — Aggregates: sum/avg/…
Source shape |
Emitted SQL |
Result type |
|---|---|---|
|
|
|
|
|
column type |
|
|
|
|
|
column type |
|
|
column type |
|
several aggregates, one row |
named tuple |
The five scalar forms are terminals — they end the chain and
produce one 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. _aggregate is the typed
one-row counterpart when several scalar facts are needed together.
8.11.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.
8.11.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.
8.11.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
8.11.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"
8.11.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" > ?
8.11.14.6. Several global aggregates in one scan
_aggregate returns a named tuple containing several global
aggregates while executing one SQL statement and scanning the filtered
source once:
let stats = _sql(
db |> select_from(type<Car>)
|> _where(_.Price >= cutoff)
|> _aggregate($(rows) => (
N = rows |> count,
Total = rows |> _select(_.Price) |> sum,
Cheapest = rows |> _select(_.Price) |> min,
Priciest = rows |> _select(_.Price) |> max,
Mean = rows |> _select(_.Price) |> average)))
The emitted query has one aggregate expression per named tuple field:
SELECT COUNT(*) AS "N",
SUM("Price") AS "Total",
MIN("Price") AS "Cheapest",
MAX("Price") AS "Priciest",
AVG("Price") AS "Mean"
FROM "Cars"
WHERE "Price" >= ?
LIMIT 1
The same _aggregate expression also works on an in-memory array,
where its block runs directly over the array. An empty SQL source
returns the daslang type default for every slot: zero for the numeric
aggregates shown above.
The first version deliberately accepts filtered table and join sources.
It rejects prior distinct, paging, ordering, grouping, set
operations, and standalone projections because those shapes require an
explicit subquery-composition rail.
8.11.14.7. 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