Aggregates and grouping

An aggregate runs one query and gives you back a value of the column's type. Grouped results go into a struct you define, through Project.

One number

adults := db.Users.Where(ant.Gt(&U.Age, 18))

total, err := ant.SumOf(ctx, adults, &U.Age)   // int
average, err := ant.AvgOf(ctx, adults, &U.Age) // float64
lowest, err := ant.MinOf(ctx, adults, &U.Age)  // int
last, err := ant.MaxOf(ctx, adults, &U.Name)   // string

MIN and SUM over no rows are NULL in SQL. Here, they're zero.

Grouping

Define what a result holds, give it a probe, and map columns and aggregates into it.

type AgeGroup struct {
	Age   int
	Users int64
}

var G = ant.Fields[AgeGroup]()

groups, err := ant.Project[AgeGroup](
	db.Users.GroupBy(&U.Age).Order(ant.Col(&U.Age).Asc()),
	ant.Into(&G.Age, &U.Age),
	ant.IntoExpr(&G.Users, ant.Count()),
).All(ctx)

SELECT "users"."age" AS "age", COUNT(*) AS "users" FROM "users" GROUP BY "users"."age" ORDER BY "users"."age"

groups is a []AgeGroup. Both sides of each mapping are probes, so the compiler checks those as well.

Filtering groups

Wherefunc (t *Table[T]) Where(c ...Cond) *Query[T]Where starts a query with conditions. filters rows before grouping, and Havingfunc (g *Grouped) Having(conds ...Cond) *GroupedHaving keeps groups that meet conds, which can use aggregates such as ant.Count().Gt(1). filters groups after.

groups, err := ant.Project[AgeGroup](
	db.Users.
		Where(ant.Gt(&U.Age, 20)).
		GroupBy(&U.Age).
		Having(ant.Count().Gt(1)).
		Order(ant.Count().Desc()).
		Limit(5),
	ant.Into(&G.Age, &U.Age),
).All(ctx)

More than one column

ant.Thenfunc Then[V any](g *Grouped, field *V) *GroupedThen groups by another column as well. adds a column to the group, and GroupByExprfunc (t *Table[T]) GroupByExpr[V any](e Expr[V]) *GroupedGroupByExpr starts a query grouped by an expression. groups by an expression.

byAgeAndName := ant.Then(db.Users.GroupBy(&U.Age), &U.Name)

decade := ant.SQL[int]("? / 10", ant.Col(&U.Age))

groups, err := ant.Project[AgeGroup](
	db.Users.GroupByExpr(decade).Order(decade.Asc()),
	ant.IntoExpr(&G.Age, decade),
	ant.IntoExpr(&G.Users, ant.Count()),
).All(ctx)

Aggregate functions

Function	Result
ant.Count()func Count() Expr[int64]Count counts rows.	int64, rows in the group
ant.CountOf(&U.Bio)func CountOf[V any](field *V) Expr[int64]CountOf counts rows where field is not NULL.	int64, rows where it isn't NULL
ant.CountDistinct(&U.Age)func CountDistinct[V any]( field *V, ) Expr[int64]CountDistinct counts the different non-NULL values of field.	int64, distinct values
ant.Sum(&U.Age)func Sum[V Number](field *V) Expr[V]Sum adds up a column.	the column's type
ant.Avg(&U.Age)func Avg[V Number](field *V) Expr[float64]Avg averages a column.	float64
ant.Min(&U.Age)func Min[V Ordered](field *V) Expr[V]Min returns a column's smallest value., ant.Max(&U.Age)func Max[V Ordered](field *V) Expr[V]Max returns a column's largest value.	the column's type

Each is an expression, so .Filter(...) narrows one aggregate without touching the rest of the query.

ant.IntoExpr(&G.Users, ant.Count().Filter(ant.Eq(&U.Name, "Ada")))

Across a join

counts, err := ant.Project[AgeGroup](
	db.Posts.Join(&P.User).GroupBy(&U.Age),
	ant.Into(&G.Age, &U.Age),
	ant.IntoExpr(&G.Users, ant.Count()),
).All(ctx)

See it tested: tests/features/querying/group_by_test.go