Aggregations (count / sum / avg / min / max + GROUP BY + window functions)

On-demand counts, sums, group-by, having, and window functions — without escaping to raw SQL.

The query builder's .count() / .aggregate({...}) / .groupBy() / .having() covers nearly every analytic query a typical SaaS app needs without raw SQL. Window functions (rowNumber, rank, lag, lead, sumOver) live in the same surface for percentile-style and running-total reads.

These are on-demand reads — they run when called, not on a schedule. For pre-computed read models that refresh periodically see Aggregates (the *.aggregate.ts file convention).

Live — an on-demand aggregation as a computed reactive query: add or toggle a todo and the { open, done, total } counts update with no refetch:

const stats = useSubscription('app', 'todos.stats')   // reactive count roll-up

Quick start

import { count, sum, avg, max, eq, queryFor } from '@voltro/database'

// How many open todos does this user have right now?
const rows = await ctx.store.query(
  queryFor(database.todos).where(eq('done', false)).count().descriptor,
)
const open = rows[0]!.count        // → number

Result rows from any aggregate are always an array with one entry per group (or exactly one entry when there's no groupBy). Reach in via [0] for the ungrouped case.

`count()` — the most common case

// Count every row in the filtered set
queryFor(database.posts).where(eq('userId', uid)).count()

// Count distinct values of a column
import { countDistinct } from '@voltro/database'
queryFor(database.posts).aggregate({
  authors: countDistinct('userId'),
})

count() defaults to COUNT(*) — every matching row, including NULLs. countDistinct(column) emits COUNT(DISTINCT column).

`aggregate({...})` — bundle multiple aggregates in one query

import { count, sum, avg, max, min } from '@voltro/database'

const rows = await ctx.store.query(
  queryFor(database.orders).where(eq('orgId', oid)).aggregate({
    total:  sum('amount'),
    avgAmt: avg('amount'),
    peak:   max('createdAt'),
    earliest: min('createdAt'),
    cnt:    count(),
  }).descriptor,
)

const stats = rows[0]!
// { total: 12_345, avgAmt: 89.5, peak: Date, earliest: Date, cnt: 138 }

The keys of the spec map become the column names on the result row. Each value is a helper:

Helper	SQL	Returns
`count()`	`COUNT(*)`	number
`count('col')`	`COUNT(col)` (non-null)	number
`countDistinct('col')`	`COUNT(DISTINCT col)`	number
`sum('col')`	`SUM(col)`	number / null
`avg('col')`	`AVG(col)`	number / null
`min('col')`	`MIN(col)`	column type / null
`max('col')`	`MAX(col)`	column type / null

SQL semantics on empty result sets: count returns 0; sum/avg/min/max return null. The framework matches this — don't write if (rows.length === 0) defensive code, the row is always there.

Fast path: `.exists()` for "is there any?"

When you only need a yes/no, avoid count() > 0 — .exists() short-circuits with SELECT 1 ... LIMIT 1:

const rows = await ctx.store.query(
  queryFor(database.users).where(eq('email', e)).exists().descriptor,
)
const emailTaken = rows[0]!.exists      // → boolean

On a 10M-row table this is the difference between an index-only scan that stops at the first match and a full count.

`groupBy(cols)` — one row per group

const rows = await ctx.store.query(
  queryFor(database.orders).where(eq('orgId', oid))
    .groupBy(['status'])
    .aggregate({ cnt: count(), total: sum('amount') })
    .descriptor,
)
// rows: [{ cnt: 12, total: 4500 }, { cnt: 5, total: 1800 }] — one row per group

Project the group key with column(). By default the result rows carry the aggregate aliases only. To fold a GROUP BY key column into the same row, add a column(name) entry to the spec — it must also appear in .groupBy([...]) (standard SQL):
import { column, count, sum } from '@voltro/database'

queryFor(database.orders).where(eq('orgId', oid))
  .groupBy(['status'])
  .aggregate({ status: column<string>('status'), cnt: count(), total: sum('amount') })
// rows: [{ status: 'open', cnt: 12, total: 4500 }, …]
Annotate the type (column<string>(...)) for a precise result row, or rely on the default string | number | boolean | Date | null union.

Chained .groupBy() calls append columns — group by orgId × status:

queryFor(database.orders)
  .groupBy(['orgId'])
  .groupBy(['status'])
  .aggregate({ cnt: count() })

Group-by works without aggregate() too (returns distinct combos), but the typical pattern is grouping + aggregating together.

`having(predicate)` — filter groups after aggregation

having predicates run AFTER aggregation; they reference aggregate aliases, not raw columns. Distinct from .where() (which becomes WHERE and runs BEFORE grouping):

import { gt } from '@voltro/database'

interface PostCount { userId: string; postCount: number }

// "How many users posted more than 10 times in the last 30 days"
const heavy = await ctx.store.query(
  queryFor(database.posts)
    .where(gt('createdAt', daysAgo(30)))            // WHERE — pre-aggregate
    .groupBy(['userId'])
    .having(gt<PostCount, 'postCount'>('postCount', 10))   // HAVING — post-aggregate
    .aggregate({ postCount: count() })
    .descriptor,
)
// rows: [{ postCount: 14 }, { postCount: 11 }, …] — one per qualifying user

Chained .having() AND-merges, same as .where().

Window functions

Window functions compute per-row aggregates over a "window" of rows without collapsing the row set — different from groupBy, which collapses. Use for ranking, running totals, "previous-row" deltas.

`rowNumber()` / `rank()` / `denseRank()`

import { rank, queryFor } from '@voltro/database'

const ranked = await ctx.store.query(
  queryFor(database.players).aggregate({
    rank: rank().over({
      partitionBy: ['teamId'],
      orderBy:     [{ column: 'score', direction: 'desc' }],
    }),
  }).descriptor,
)
// Each row: { rank } — rank restarts at 1 per team

Note: to carry a plain column (id, name) alongside the window expression, add a column(name) entry to the spec — it projects "col" AS "alias" into the same result row.

rowNumber() always returns sequential 1, 2, 3 — ties get distinct numbers. rank() gives ties the same number, then skips (1, 1, 3, 4…). denseRank() gives ties the same number, no skip (1, 1, 2, 3…).

`lag()` / `lead()` — previous / next row

import { lag, lead } from '@voltro/database'

queryFor(database.scores).aggregate({
  prevScore:  lag('score').over({ partitionBy: ['userId'], orderBy: [{ column: 'createdAt', direction: 'asc' }] }),
  nextScore:  lead('score').over({ partitionBy: ['userId'], orderBy: [{ column: 'createdAt', direction: 'asc' }] }),
})

Both accept an optional offset — lag('score', 3) looks 3 rows back.

`sumOver()` / `avgOver()` — running totals

import { sumOver } from '@voltro/database'

queryFor(database.ledger).aggregate({
  runningSum: sumOver('amount').over({
    orderBy: [{ column: 'createdAt', direction: 'asc' }],
  }),
})
// Each row: running total of `amount` up to and including this row

Combine partitionBy + orderBy for per-group running totals ("total spend per user, sorted by date").

Reactivity

count() and aggregate() subscriptions ARE reactive. The dispatcher subscribes to the source table; any write triggers a re-run of the same aggregate SQL.

Window-function queries are reactive — coarsely. The matcher can't bucket on a window result (a rank shifts when ANY partition peer changes), so the engine widens the dependency to the whole source table: a write to it re-runs the query. That's correct but coarser than a plain query's per-field pre-filter — every write to the source table re-evaluates the window. Keep the source set bounded (a top-N leaderboard, not a 10M-row scan), or materialise it on a schedule with an aggregate.

Cross-dialect

Standard SQL — postgres, mysql 8+, mariadb 10.2+, mssql, sqlite 3.25+, turso all support these forms with identical syntax. The framework's compiler doesn't dispatch per dialect for any of the helpers above.

Aggregations (count / sum / avg / min / max + GROUP BY + window functions)

#Quick start

#count() — the most common case

#aggregate({...}) — bundle multiple aggregates in one query

#Fast path: .exists() for "is there any?"

#groupBy(cols) — one row per group

#having(predicate) — filter groups after aggregation

#Window functions

#rowNumber() / rank() / denseRank()

#lag() / lead() — previous / next row

#sumOver() / avgOver() — running totals

#Reactivity

#Cross-dialect

#See also