Aggregates and grouping

Totals, counts and groups in the select list, and buckets of time.

Put count, sum, avg, min, max, first or last in the select list to fold the rows a query finds into one, and add group for one row per distinct set of keys.

Run in the shell, answers and all; the timings are from one run.

fenec> create collection sales (region text, qty int, price int, at timestamp);
collection `sales` created
fenec> put sales [{region: "north", qty: 2, price: 500, at: "2026-05-03T09:10:00Z"}, {region: "north", qty: 1, price: 900, at: "2026-05-03T09:40:00Z"}, {region: "south", qty: 4, price: 300, at: "2026-05-03T10:05:00Z"}, {region: "south", qty: 3, price: 350, at: "2026-05-03T10:50:00Z"}, {region: "north", qty: 5, price: 400, at: "2026-05-03T11:15:00Z"}];
5 records (196.33µs)
fenec> get sales select count(*) as n, sum(qty) as units, max(price) as top
n  units  top
-  -----  ---
5  15     900
(1 rows, 152.33µs)
fenec> get sales select region, count(*) as n, sum(qty * price) as revenue group region order revenue desc
region  n  revenue
------  -  -------
north   3  3900
south   2  2250
(2 rows, 171.04µs)
fenec> get sales select bucket(at, 1h) as hour, sum(qty) as units group hour
hour                  units
--------------------  -----
2026-05-03T09:00:00Z  3
2026-05-03T10:00:00Z  7
2026-05-03T11:00:00Z  5
(3 rows, 149.96µs)
fenec> get sales select region, sum(qty) as units group region having sum(qty) >= 8
region  units
------  -----
north   8
(1 rows, 168.46µs)

An aggregate folds any expression, sum(qty * price); bucket(at, 1h) truncates a timestamp to the hour it falls in, and its intervals and time zones are under Functions over a row; and having keeps the groups it holds for. Bars, VWAP, distinct users and funnels are on Analytics and market data.

Aggregates

Put count(*), count(…), count(distinct …), sum, avg, min, max, first or last in the select list to get totals. Add group for one row per distinct set of its keys' values; a list of the keys alone, select city group city, answers each set once, as SQL's select distinct city does.

get orders select count(*), sum(total), avg(total), min(at), max(at)
get orders select status, count(*), avg(total) where year = 2026 group status
get orders select customer, sum(total) group customer order sum(total) desc limit 10
get events select bucket(at, 1h) as hour, name, count(distinct user) as users group hour, name
get ticks select bucket(at, 1m) as bar, first(px by at) as open, max(px) as high, min(px) as low,
  last(px by at) as close, sum(qty) as volume where sym = $1 group bar
get ticks select sym, sum(px * qty) / sum(qty) as vwap group sym
get events select props.plan, sum(props.v) group props.plan

import { connect, expr, bucket, countDistinct, first, last } from '@fenecdb/web/client'

await db.from('orders')
  .select('count(*)', 'sum(total)', 'avg(total)', 'min(at)', 'max(at)')
  .rows();

await db.from('orders')
  .select('status', 'count(*)', 'avg(total)')
  .where('year', 2026)
  .group('status')
  .rows();

await db.from('orders')
  .select('customer', 'sum(total)')
  .group('customer')
  .order('sum(total)', 'desc')
  .limit(10)
  .rows();

await db.from('events')
  .select(
    bucket('at', '1h').as('hour'),
    'name',
    countDistinct('user').as('users')
  )
  .group('hour', 'name')
  .rows();

await db.from('ticks')
  .select(
    bucket('at', '1m').as('bar'),
    first('px', 'at').as('open'),
    expr('max(px)').as('high'),
    expr('min(px)').as('low'),
    last('px', 'at').as('close'),
    expr('sum(qty)').as('volume')
  )
  .where('sym', 'S07')
  .group('bar')
  .rows();

await db.from('ticks')
  .select('sym', expr('sum(px * qty) / sum(qty)').as('vwap'))
  .group('sym')
  .rows();

await db.from('events')
  .select('props.plan', 'sum(props.v)')
  .group('props.plan')
  .rows();

from fenecdb import Client, expr, bucket, count_distinct, first, last

(db.collection("orders")
    .select("count(*)", "sum(total)", "avg(total)", "min(at)", "max(at)")
    .rows())

(db.collection("orders")
    .select("status", "count(*)", "avg(total)")
    .where("year", 2026)
    .group("status")
    .rows())

(db.collection("orders")
    .select("customer", "sum(total)")
    .group("customer")
    .order("sum(total)", "desc")
    .limit(10)
    .rows())

(db.collection("events")
    .select(
        bucket("at", "1h").as_("hour"),
        "name",
        count_distinct("user").as_("users")
    )
    .group("hour", "name")
    .rows())

(db.collection("ticks")
    .select(
        bucket("at", "1m").as_("bar"),
        first("px", "at").as_("open"),
        expr("max(px)").as_("high"),
        expr("min(px)").as_("low"),
        last("px", "at").as_("close"),
        expr("sum(qty)").as_("volume")
    )
    .where("sym", "S07")
    .group("bar")
    .rows())

(db.collection("ticks")
    .select("sym", expr("sum(px * qty) / sum(qty)").as_("vwap"))
    .group("sym")
    .rows())

(db.collection("events")
    .select("props.plan", "sum(props.v)")
    .group("props.plan")
    .rows())

import fenecdb "github.com/fenecdb/fenecdb/integrations/go"

db.From("orders").
	Select("count(*)", "sum(total)", "avg(total)", "min(at)", "max(at)").
	Rows(ctx)

db.From("orders").
	Select("status", "count(*)", "avg(total)").
	Where("year", "=", 2026).
	Group("status").
	Rows(ctx)

db.From("orders").
	Select("customer", "sum(total)").
	Group("customer").
	Order("sum(total)", "desc").
	Limit(10).
	Rows(ctx)

db.From("events").
	Select(
		fenecdb.Bucket("at", "1h").As("hour"),
		"name",
		fenecdb.CountDistinct("user").As("users"),
	).
	Group("hour", "name").
	Rows(ctx)

db.From("ticks").
	Select(
		fenecdb.Bucket("at", "1m").As("bar"),
		fenecdb.First("px", "at").As("open"),
		fenecdb.Expr("max(px)").As("high"),
		fenecdb.Expr("min(px)").As("low"),
		fenecdb.Last("px", "at").As("close"),
		fenecdb.Expr("sum(qty)").As("volume"),
	).
	Where("sym", "=", "S07").
	Group("bar").
	Rows(ctx)

db.From("ticks").
	Select("sym", fenecdb.Expr("sum(px * qty) / sum(qty)").As("vwap")).
	Group("sym").
	Rows(ctx)

db.From("events").
	Select("props.plan", "sum(props.v)").
	Group("props.plan").
	Rows(ctx)

using FenecDb;

await db.From("orders")
    .Select("count(*)", "sum(total)", "avg(total)", "min(at)", "max(at)")
    .RowsAsync();

await db.From("orders")
    .Select("status", "count(*)", "avg(total)")
    .Where("year", 2026)
    .Group("status")
    .RowsAsync();

await db.From("orders")
    .Select("customer", "sum(total)")
    .Group("customer")
    .Order("sum(total)", "desc")
    .Limit(10)
    .RowsAsync();

await db.From("events")
    .Select(
        Computed.Bucket("at", "1h").As("hour"),
        "name",
        Computed.CountDistinct("user").As("users")
    )
    .Group("hour", "name")
    .RowsAsync();

await db.From("ticks")
    .Select(
        Computed.Bucket("at", "1m").As("bar"),
        Computed.First("px", "at").As("open"),
        Computed.Expr("max(px)").As("high"),
        Computed.Expr("min(px)").As("low"),
        Computed.Last("px", "at").As("close"),
        Computed.Expr("sum(qty)").As("volume")
    )
    .Where("sym", "S07")
    .Group("bar")
    .RowsAsync();

await db.From("ticks")
    .Select("sym", Computed.Expr("sum(px * qty) / sum(qty)").As("vwap"))
    .Group("sym")
    .RowsAsync();

await db.From("events")
    .Select("props.plan", "sum(props.v)")
    .Group("props.plan")
    .RowsAsync();

import FenecDB

try await db.from("orders")
    .select("count(*)", "sum(total)", "avg(total)", "min(at)", "max(at)")
    .rows()

try await db.from("orders")
    .select("status", "count(*)", "avg(total)")
    .where("year", 2026)
    .group("status")
    .rows()

try await db.from("orders")
    .select("customer", "sum(total)")
    .group("customer")
    .order("sum(total)", "desc")
    .limit(10)
    .rows()

try await db.from("events")
    .select(
        .bucket("at", "1h").as("hour"),
        "name",
        .countDistinct("user").as("users")
    )
    .group("hour", "name")
    .rows()

try await db.from("ticks")
    .select(
        .bucket("at", "1m").as("bar"),
        .first("px", by: "at").as("open"),
        .expr("max(px)").as("high"),
        .expr("min(px)").as("low"),
        .last("px", by: "at").as("close"),
        .expr("sum(qty)").as("volume")
    )
    .where("sym", "S07")
    .group("bar")
    .rows()

try await db.from("ticks")
    .select("sym", .expr("sum(px * qty) / sum(qty)").as("vwap"))
    .group("sym")
    .rows()

try await db.from("events")
    .select("props.plan", "sum(props.v)")
    .group("props.plan")
    .rows()

import com.fenecdb.*

db.from("orders")
    .select("count(*)", "sum(total)", "avg(total)", "min(at)", "max(at)")
    .rows()

db.from("orders")
    .select("status", "count(*)", "avg(total)")
    .where("year", 2026)
    .group("status")
    .rows()

db.from("orders")
    .select("customer", "sum(total)")
    .group("customer")
    .order("sum(total)", "desc")
    .limit(10)
    .rows()

db.from("events")
    .select(
        Computed.bucket("at", "1h").alias("hour"),
        "name",
        Computed.countDistinct("user").alias("users")
    )
    .group("hour", "name")
    .rows()

db.from("ticks")
    .select(
        Computed.bucket("at", "1m").alias("bar"),
        Computed.first("px", "at").alias("open"),
        Computed.expr("max(px)").alias("high"),
        Computed.expr("min(px)").alias("low"),
        Computed.last("px", "at").alias("close"),
        Computed.expr("sum(qty)").alias("volume")
    )
    .where("sym", "S07")
    .group("bar")
    .rows()

db.from("ticks")
    .select("sym", Computed.expr("sum(px * qty) / sum(qty)").alias("vwap"))
    .group("sym")
    .rows()

db.from("events")
    .select("props.plan", "sum(props.v)")
    .group("props.plan")
    .rows()

import 'package:fenecdb/fenecdb.dart';

await db.from('orders')
    .select(['count(*)', 'sum(total)', 'avg(total)', 'min(at)', 'max(at)'])
    .rows();

await db.from('orders')
    .select(['status', 'count(*)', 'avg(total)'])
    .where('year', 2026)
    .group('status')
    .rows();

await db.from('orders')
    .select(['customer', 'sum(total)'])
    .group('customer')
    .order('sum(total)', 'desc')
    .limit(10)
    .rows();

await db.from('events')
    .select([
        Computed.bucket('at', '1h').as('hour'),
        'name',
        Computed.countDistinct('user').as('users')
    ])
    .group(['hour', 'name'])
    .rows();

await db.from('ticks')
    .select([
        Computed.bucket('at', '1m').as('bar'),
        Computed.first('px', 'at').as('open'),
        Computed.expr('max(px)').as('high'),
        Computed.expr('min(px)').as('low'),
        Computed.last('px', 'at').as('close'),
        Computed.expr('sum(qty)').as('volume')
    ])
    .where('sym', 'S07')
    .group('bar')
    .rows();

await db.from('ticks')
    .select(['sym', Computed.expr('sum(px * qty) / sum(qty)').as('vwap')])
    .group('sym')
    .rows();

await db.from('events')
    .select(['props.plan', 'sum(props.v)'])
    .group('props.plan')
    .rows();

sends

get orders select count(*), sum(total), avg(total), min(at), max(at)
get orders select status, count(*), avg(total) where year = $1 group status
-- [2026]
get orders select customer, sum(total) group customer order sum(total) desc limit $1
-- [10]
get events select bucket(at, 1h) as hour, name, count(distinct user) as users group hour, name
get ticks select bucket(at, 1m) as bar, first(px by at) as open, max(px) as high, min(px) as low, last(px by at) as close, sum(qty) as volume where sym = $1 group bar
-- ["S07"]
get ticks select sym, sum(px * qty) / sum(qty) as vwap group sym
get events select props.plan, sum(props.v) group props.plan

Nulls are skipped, as in SQL, by everything but count(*).
An aggregate folds any expression — sum(px * qty) — and an item can be an expression over aggregates and keys — sum(px * qty) / sum(qty).
Groups can be ordered and paged by any column of the list. Without group, order, limit and offset pick the rows the one row folds: get deliveries select count(*), avg(km) where courier = $1 order at desc limit 50 is the latest 50.
A lookup hangs off the groups, keyed by a column of the list, and a lookup level's list may aggregate, each parent's children folded.
Aggregates do not combine with near or match.

Each aggregate, group keys and having

A select list with an aggregate in it folds the rows the query finds — through the same indexes any where reaches — into one row, or, with group, one row per distinct set of the keys' values. Each column answers under its name, in the order the list is written: the name as gives it, or for a field or an aggregate of one the text it was written as — sum(total), count(distinct user), first(px) (its by left out). Anything else needs as.

Keys. group takes one key or several, each a field, a path, or an expression — group sym, bucket(at, 1m). A name the list gives an item with as stands for that item, before a field of the same name: select bucket(at, 1h) as hour, count(*) group hour. A plain item of the list is a key itself, or is refused as neither aggregated nor grouped by; an expression written out again in group is not matched to the list's — name it once, with as.
count(*) counts rows. count(distinct e) counts the values e takes, a null none, two values the same when their encodings are — as group tells its keys apart. It is exact: every value is held once a group, up to a million over every group and item of the query — about 70 MB — and a query past that is refused, never counted short.
sum and avg take numbers: an int sum is an int, and one past 64 bits is an error rather than a wrapped number; a float among the values makes the sum a float, and an average is one. min and max take any value with an order — numbers, timestamp, text, bool — a text field's in its collation. A field whose type cannot be folded is refused before any row is read, an expression's value at the first row that cannot.
approx_count_distinct(e) counts distinct values in a HyperLogLog sketch of 2¹⁴ registers, 16 KB a group at most, with a standard error of 0.81% at any count and no million to stop at. hll_accumulate(e) answers the sketch as bytes, hll_combine(s) merges sketches and the function hll_estimate(s) reads a count off one, so days kept as sketches count a month. A query's sketches are held to 64 MB.
having <expr> after group keeps the groups it holds for, before order and limit: it reads the keys, the list's columns by name and any aggregate, group user having count(*) >= 2. count after it answers how many groups passed.
first(e by k) and last(e by k) are the value of e on the group's row least, or greatest, by k — a bar's open and close are first(px by at) and last(px by at), whatever order the ticks were written in. A tie on k goes to the row written first for first and last for last; without by, the order is the one the rows were written in. A row whose value or key is null is passed over, as every aggregate passes a null over. The query's order orders the groups, not the rows inside them, so it says nothing to these.
Nulls are skipped, as SQL skips them, by everything but count(*). Over no rows — or only nulls — a sum, an average, a minimum and a maximum are null, not 0.
Groups come out by their keys, in the order group names them, the null key first, unless order names a column of the list — order sum(total) desc, order vwap — and limit and offset page them.
Ungrouped, a list answers one row, which an order or a page would have nothing to do with; so there order, limit and offset pick the rows it folds, as they pick a get's page — an @sorted field's index walked to it — and the one row folds those: select avg(km) order at desc limit 50 is the mean of the latest 50, what SQL writes as a subquery. Its keys are the rows' values; a column of the list among them is refused.
A lookup after the list hangs off its rows — the groups, or the one row — and names a column of the list as the parent's key: get orders select courier, count(*) as n group courier order n desc limit 10 lookup couriers on user = courier select name is the ten busiest couriers with their names. The column holds a field's values: a key, its bucket, its min, max, first or last; a sum's or a count's is refused, having no type to hold the child's key to. required there drops the groups no child matches, before order and limit, and count after group counts those kept.
What does not combine is what ranks rows an aggregate collapses: near and match.

How it runs. Every item is bound to the collection once a query: each field and path the list reads is decoded in one pass a row, a field folded as it is is read with nothing copied, and a bucket over a constant interval works the interval out once. Rows written in time order come a bucket at a time, so the group the row before went to is asked first. A count(*) by bucket of a timestamp or int field with an ordered index (@sorted, @ttl), whose filter is a range of that field and nothing more, reads the index's keys and no row; explain says so.

A million rows in 100 groups, select cat, count(*), avg(price) group cat, took 80 ms in process where PostgreSQL 17 — one worker, as make pgvector-up runs it — took 189 ms of execution time on the same rows; the whole set's count, sum, minimum and maximum, 37 ms; the same groups over the tenth a @sorted range found, 9.3 ms. The rows are read, not an index: a filter narrows them, nothing precomputes the totals. The questions analytics and market data ask — bars, VWAP, distinct users, counts by the minute — are on Analytics and market data, each with what it costs over a million rows.

Totals per group

get orders select status, count(*), sum(total) where year = 2026 group status
  order sum(total) desc limit 3

import { connect } from '@fenecdb/web/client'

await db.from('orders')
  .select('status', 'count(*)', 'sum(total)')
  .where('year', 2026)
  .group('status')
  .order('sum(total)', 'desc')
  .limit(3)
  .rows();

from fenecdb import Client

(db.collection("orders")
    .select("status", "count(*)", "sum(total)")
    .where("year", 2026)
    .group("status")
    .order("sum(total)", "desc")
    .limit(3)
    .rows())

import fenecdb "github.com/fenecdb/fenecdb/integrations/go"

db.From("orders").
	Select("status", "count(*)", "sum(total)").
	Where("year", "=", 2026).
	Group("status").
	Order("sum(total)", "desc").
	Limit(3).
	Rows(ctx)

using FenecDb;

await db.From("orders")
    .Select("status", "count(*)", "sum(total)")
    .Where("year", 2026)
    .Group("status")
    .Order("sum(total)", "desc")
    .Limit(3)
    .RowsAsync();

import FenecDB

try await db.from("orders")
    .select("status", "count(*)", "sum(total)")
    .where("year", 2026)
    .group("status")
    .order("sum(total)", "desc")
    .limit(3)
    .rows()

import com.fenecdb.*

db.from("orders")
    .select("status", "count(*)", "sum(total)")
    .where("year", 2026)
    .group("status")
    .order("sum(total)", "desc")
    .limit(3)
    .rows()

import 'package:fenecdb/fenecdb.dart';

await db.from('orders')
    .select(['status', 'count(*)', 'sum(total)'])
    .where('year', 2026)
    .group('status')
    .order('sum(total)', 'desc')
    .limit(3)
    .rows();

sends

get orders select status, count(*), sum(total) where year = $1 group status order sum(total) desc limit $2
-- [2026, 3]

Bars by the minute

get ticks select bucket(at, 1m) as bar, first(px by at) as open, max(px) as high,
  min(px) as low, last(px by at) as close, sum(qty) as volume
  where sym = $1 and at >= $2 group bar

import { connect, expr, bucket, first, last } from '@fenecdb/web/client'

await db.from('ticks')
  .select(
    bucket('at', '1m').as('bar'),
    first('px', 'at').as('open'),
    expr('max(px)').as('high'),
    expr('min(px)').as('low'),
    last('px', 'at').as('close'),
    expr('sum(qty)').as('volume')
  )
  .where('sym', 'S07')
  .where('at', '>=', '2026-05-03T09:00:00Z')
  .group('bar')
  .rows();

from fenecdb import Client, expr, bucket, first, last

(db.collection("ticks")
    .select(
        bucket("at", "1m").as_("bar"),
        first("px", "at").as_("open"),
        expr("max(px)").as_("high"),
        expr("min(px)").as_("low"),
        last("px", "at").as_("close"),
        expr("sum(qty)").as_("volume")
    )
    .where("sym", "S07")
    .where("at", ">=", "2026-05-03T09:00:00Z")
    .group("bar")
    .rows())

import fenecdb "github.com/fenecdb/fenecdb/integrations/go"

db.From("ticks").
	Select(
		fenecdb.Bucket("at", "1m").As("bar"),
		fenecdb.First("px", "at").As("open"),
		fenecdb.Expr("max(px)").As("high"),
		fenecdb.Expr("min(px)").As("low"),
		fenecdb.Last("px", "at").As("close"),
		fenecdb.Expr("sum(qty)").As("volume"),
	).
	Where("sym", "=", "S07").
	Where("at", ">=", "2026-05-03T09:00:00Z").
	Group("bar").
	Rows(ctx)

using FenecDb;

await db.From("ticks")
    .Select(
        Computed.Bucket("at", "1m").As("bar"),
        Computed.First("px", "at").As("open"),
        Computed.Expr("max(px)").As("high"),
        Computed.Expr("min(px)").As("low"),
        Computed.Last("px", "at").As("close"),
        Computed.Expr("sum(qty)").As("volume")
    )
    .Where("sym", "S07")
    .Where("at", ">=", "2026-05-03T09:00:00Z")
    .Group("bar")
    .RowsAsync();

import FenecDB

try await db.from("ticks")
    .select(
        .bucket("at", "1m").as("bar"),
        .first("px", by: "at").as("open"),
        .expr("max(px)").as("high"),
        .expr("min(px)").as("low"),
        .last("px", by: "at").as("close"),
        .expr("sum(qty)").as("volume")
    )
    .where("sym", "S07")
    .where("at", ">=", "2026-05-03T09:00:00Z")
    .group("bar")
    .rows()

import com.fenecdb.*

db.from("ticks")
    .select(
        Computed.bucket("at", "1m").alias("bar"),
        Computed.first("px", "at").alias("open"),
        Computed.expr("max(px)").alias("high"),
        Computed.expr("min(px)").alias("low"),
        Computed.last("px", "at").alias("close"),
        Computed.expr("sum(qty)").alias("volume")
    )
    .where("sym", "S07")
    .where("at", ">=", "2026-05-03T09:00:00Z")
    .group("bar")
    .rows()

import 'package:fenecdb/fenecdb.dart';

await db.from('ticks')
    .select([
        Computed.bucket('at', '1m').as('bar'),
        Computed.first('px', 'at').as('open'),
        Computed.expr('max(px)').as('high'),
        Computed.expr('min(px)').as('low'),
        Computed.last('px', 'at').as('close'),
        Computed.expr('sum(qty)').as('volume')
    ])
    .where('sym', 'S07')
    .where('at', '>=', '2026-05-03T09:00:00Z')
    .group('bar')
    .rows();

sends

get ticks select bucket(at, 1m) as bar, first(px by at) as open, max(px) as high, min(px) as low, last(px by at) as close, sum(qty) as volume where sym = $1 and at >= $2 group bar
-- ["S07", "2026-05-03T09:00:00Z"]