Grouping and aggregates

Group rows with GroupBy, filter groups with Having, and compute count, min, max, avg, sum, stdev, var and their _distinct variants through ``

Prerequisites: Querying and projections · Filtering (WHERE) · Sorting and paging

Overview

EntityBuilder<T>.GroupBy(...) attaches a GROUP BY clause and EntityBuilder<T>.Having(...) attaches a HAVING clause that filters the groups. Both are clauses on the builder, so they are combined with Where, OrderBy, Limit/Page and the projection exactly like any other query:

var rows = dataContext.From<IComplexEntity>()
    .GroupBy(e => new { e.Int })
    .Select(e => new { e.Int, count = SqlFunctions.Sql.count() })
    .ToList();

The aggregate functions live on Sql and are only meaningful inside a projection or a Having predicate. They are named exactly as follows:

Function	Result	Function	Result
`count()`	`int`	`count_distinct(...)`	`int`
`count_big()`	`long`	`count_big_distinct(...)`	`long`
`min(x)`	same as `x`	`avg_distinct(x)`	same as `avg(x)`
`max(x)`	same as `x`	`sum_distinct(x)`	same as `x`
`avg(x)`	`double?` / `float?` / `decimal?` (by input type)	`stdev_distinct(x)`	same as `x`
`sum(x)`	same as `x`	`stdevp_distinct(x)`	same as `x`
`stdev(x)`	same as `x`	`var_distinct(x)`	same as `x`
`stdevp(x)`	same as `x`	`varp_distinct(x)`	same as `x`
`var(x)`	same as `x`	`varp(x)`	same as `x`

Called with no arguments, count() / count_big() count rows (count(*)); called with one or more expressions they count non-null values of those expressions. The other functions take an expression argument. The _distinct suffix adds distinct inside the parentheses.

The common aggregates also have convenience terminals on EntityBuilder<T>: Count, Min(x), Max(x), Avg(x), Sum(x), Stdev(x), Stdevp(x), Var(x) and Varp(x), each with an ...Async twin and an overload that accepts positional parameters. count, count_big, count_distinct and count_big_distinct have no entity terminal and are used through ``

AVG result types and argument policy

avg does not return its input type: an integral column produces a fractional result, so avg over [1, 2] is 1.5 instead of the integral value earlier versions returned (2; 1 on SQL Server, where native integer AVG truncates). The result is nullable on every provider, so an empty or all-NULL input yields null rather than an unguarded read.

Input	`avg(x)` result
`byte`, `sbyte`, `short`, `ushort`, `int`, `uint`, `long`, `ulong` and their nullable forms	`double?`
`float`, `float?`	`float?`
`double`, `double?`	`double?`
`decimal`, `decimal?`	`decimal?`

The argument policy is per provider. SQL Server evaluates AVG over a non-floating argument as an integer, so the argument is widened inside the aggregate; the other dialects keep the native argument. The policy applies to avg, avg_distinct and the windowed avg_over alike:

Provider	Rendered argument
SQLite, PostgreSQL, MySQL, MariaDB, ClickHouse	`avg(id)`
SQL Server	`avg(cast(id as float))`

The result is nullable: Avg returns double? (or float?/decimal? for those inputs). Keep the nullable result or unwrap it explicitly; an integral target does not compile:

int average = dataContext.From<ISimpleEntity>().Avg(e => e.Id);        // does not compile
var average = dataContext.From<ISimpleEntity>().Avg(e => e.Id);        // double?
var direct  = dataContext.From<ISimpleEntity>().Select(e => SqlFunctions.Sql.avg(e.Id)).First(); // double?

Let overload resolution pick the declaration from the selector type rather than naming the generic arguments explicitly.

GroupBy

var rows = dataContext.From<IComplexEntity>()
    .GroupBy(e => new { e.Int })
    .Select(e => new { e.Int, count = SqlFunctions.Sql.count() })
    .ToList();

select nullableint as 'Int', count(*)
 from complex_entity group by nullableint

For a single grouping column the anonymous wrapper is optional: pass the scalar expression directly. GroupBy(e => e.Int) and GroupBy(e => new { e.Int }) produce the same SQL and share the same query plan, so the two forms can be used interchangeably:

var rows = dataContext.From<IComplexEntity>()
    .GroupBy(e => e.Int)
    .Select(e => new { e.Int, count = SqlFunctions.Sql.count() })
    .ToList();

The Output: tables below show the rows returned by each example against the integration-test seed data (tests/nextorm.integration.tests/Providers/TestProviders/SqliteTestProvider.cs).

Output:

Int	count
null	1
1	2

Having

Having filters groups after aggregation, where a Where filters rows before it:

var rows = dataContext.From<IComplexEntity>()
    .GroupBy(e => new { e.Int })
    .Having(e => SqlFunctions.Sql.count() > 1)
    .Select(e => new { e.Int, count = SqlFunctions.Sql.count() })
    .ToList();

select nullableint as 'Int', count(*)
 from complex_entity group by nullableint having (count(*) > 1)

Output:

Int	count
1	2

Where and Having can be combined on the same query; the Where is applied before grouping:

var rows = dataContext.From<IComplexEntity>()
    .Where(e => e.Int != null)
    .GroupBy(e => new { e.Int })
    .Having(e => SqlFunctions.Sql.count() > 1)
    .Select(e => new { e.Int, count = SqlFunctions.Sql.count() })
    .ToList();

Output:

Int	count
1	2

Grouping with sort and limit

A grouped projection is sorted and paged like any other query. Ordering by the ordinal of a selected column keeps the query provider-portable:

// Order by the aggregate (never null), then take the first group.
var first = dataContext.From<IComplexEntity>()
    .GroupBy(e => new { e.Int })
    .Select(e => new { e.Int, count = SqlFunctions.Sql.count() })
    .OrderBy(2, OrderDirection.Asc)
    .First();

select nullableint as 'Int', count(*)
 from complex_entity group by nullableint order by 2 limit 1

Output:

Int	count
null	1

var top = dataContext.From<IComplexEntity>()
    .Where(e => e.Int != null)
    .GroupBy(e => new { e.Int })
    .Select(e => new { e.Int, count = SqlFunctions.Sql.count() })
    .OrderBy(1, OrderDirection.Desc)
    .First();

Output:

Int	count
1	2

Note that NULL ordering differs between providers (PostgreSQL sorts NULLs first on a descending sort, SQLite last), which is why the sort example excludes the NULL group.

Grouping inferred from the projection: SelectGroup

SelectGroup is shipped in the NextORM.Extensions package (using NextORM.Extensions;). It takes the same projection lambda as Select, but instead of requiring an explicit GroupBy it infers the grouping keys from that projection. Every row-dependent expression that contains no aggregate becomes a key; the aggregate calls fold each group. The projection keeps its exact shape - there is no client-side grouping, no dynamic type and no reflection-emit merging of a flat result type. The projection is typed on the source entity: call it with both generic arguments (.SelectGroup<TEntity, TResult>(...)) or omit them. A quarterly example:

ctx.From<Product>()
   .SelectGroup(p => new
   {
       ProductID        = p.Item1.ProductId,
       ProductName      = p.Item3.Name,
       SalesQuarter     = SqlFunctions.Sql.date_trunc("quarter", p.Item2.OrderDate),
       QuarterlyRevenue = SqlFunctions.Sql.sum(p.Item1.LineTotal),
       QuarterlyQty     = SqlFunctions.Sql.sum(p.Item1.OrderQty),
   });

ProductID, ProductName and the whole date_trunc('quarter', OrderDate) call become GROUP BY keys, while the two sum calls stay aggregates. Results and SQL are equivalent to the explicit GroupBy(...).Select(...) form with the same keys. A compact example and its SQL:

var rows = dataContext.From<IComplexEntity>()
    .SelectGroup(e => new { e.Int, Total = SqlFunctions.Sql.sum(e.Id) })
    .ToList();

select nullableint as 'Int', sum(id) as 'Total'
 from complex_entity
 group by nullableint

The only key became nullableint while sum(id) stayed an aggregate - no explicit GroupBy is needed.

Inference rules:

Maximal row-dependent subexpressions. The analyzer collects the largest aggregate-free subexpression of each output that depends on the current row, instead of descending to individual columns.
First-appearance order. Keys are emitted in the order their expressions first appear in the projection, which makes the rendered GROUP BY deterministic.
Constants and captured values are not keys. A literal or a captured local neither groups nor shifts the key order.
Duplicates collapse structurally. Two structurally equal key expressions produce one key.
A whole scalar function stays one key. date_trunc('quarter', OrderDate) is the key; the raw OrderDate is not added beside it.
Subexpression granularity is preserved. sum(Amount) + Adjustment infers Adjustment; sum(Amount) + Price * Qty infers Price * Qty, not Price and Qty separately.
Aggregate calls are opaque. The arguments of an aggregate, its DISTINCT input and its FILTER predicate never leak into the grouping keys.
The projection is preserved. The original projection is rendered as written; no key is rewritten, no value is folded on the client and the result shape is unchanged.

Degenerate projections resolve without inventing a grouping:

Keys without aggregates return the unique key combinations through GROUP BY; it is not a row-by-row select.
Aggregates without keys is a global aggregation: no GROUP BY, exactly one row, and the provider's existing empty-input behaviour for count, sum and NULL is preserved.
A row-independent projection without aggregates is DISTINCT-like: each distinct value is returned once and an empty source returns zero rows instead of an invalid empty GROUP BY.

Having and an expression OrderBy are validated against the inferred keys: every row expression they mention must be covered by an inferred key, be an aggregate, or be a captured/constant value. An uncovered row member throws QueryPreparationException before the command reaches the database - the grouping is never widened silently and functional dependencies are not inferred. A captured threshold is therefore valid, a bare outer-row column is not. An OrderBy by ordinal keeps its existing bounds validation and adds no keys. Where stays a pre-group filter and takes no part in inference.

The following combinations throw QueryPreparationException:

an existing explicit GroupBy, GroupByRollup, GroupByCube, GroupByGroupingSets or WithTotals - the two grouping mechanisms are never merged or overridden;
window functions in the projection, Having or an expression OrderBy;
scalar or correlated subqueries in those expressions, including inside aggregate arguments and inside a FILTER predicate. That rejection is unconditional and independent of key inference; a subquery in Where remains supported.

The in-memory provider is not supported: both operators throw NotSupportedException before any plan is built, so inferred grouping always runs on a real database.

SelectGroupHaving

SelectGroupHaving(projection, condition) uses the same key inference as SelectGroup and ANDs condition into the effective HAVING. condition is typed on the projection result (TResult), never on the source entity: every member of the result is rebound to the projection expression that produces it before validation, so r => r.Rows > 1 becomes the same HAVING as the underlying count(...) > 1, while a scalar projection makes the condition parameter the projected value itself (f => f > 5). Keys are inferred only from projection; condition never adds a key. An existing source Having is AND-ed with condition before validation. A condition member the projection does not bind (or the result as a whole), and an uncovered row expression in projection or in the effective HAVING, fail with QueryPreparationException before the database is reached. The three outcomes (keyed grouping, global aggregation, constant projection) are the same as for SelectGroup.

For example, r => r.Count > 1 becomes a HAVING over the underlying count(*), while the key is still inferred from the projection only:

var rows = dataContext.From<IComplexEntity>()
    .SelectGroupHaving(
        e => new { e.Int, Count = SqlFunctions.Sql.count() },
        r => r.Count > 1)
    .ToList();

select nullableint as 'Int', count(*) as 'Count'
 from complex_entity
 group by nullableint
 having (count(*) > 1)

Both operators throw NotSupportedException (message contains in-memory) when executed against an InMemoryDataContext.

The packaged operators are themselves ordinary query components built on the public IQueryComponent contract: a third-party package can add its own structural operator the same way, without friend access, a registry or raw SQL. See Extending query construction with query components.

ROLLUP and CUBE

GroupByRollup(...) and GroupByCube(...) attach the ANSI super-aggregate modifiers. ROLLUP adds every prefix subtotal plus the grand total; CUBE adds every combination:

var rows = dataContext.From<IComplexEntity>()
    .GroupByRollup(e => new { e.Int, e.Boolean })
    .Select(e => new { e.Int, e.Boolean, count = SqlFunctions.Sql.count() })
    .ToList();

-- SQL Server / PostgreSQL / SQLite
select nullableint as 'Int', b as 'Boolean', count(*)
 from complex_entity group by rollup (nullableint, b)
-- MySQL / MariaDB / ClickHouse
select nullableint as 'Int', b as 'Boolean', count(*)
 from complex_entity group by nullableint, b with rollup

ROLLUP is available on every SQL provider; CUBE is not available on MySQL/MariaDB. The in-memory provider supports neither modifier and throws NotSupportedException.

WITH TOTALS

ClickHouse adds WITH TOTALS (SupportsGroupByWithTotals) through .WithTotals() after any grouping: the query returns the group rows plus one row with the totals over all groups. It can be combined with ROLLUP/CUBE but not with GROUPING SETS. The official ClickHouse.Driver does not surface the totals row, so nextorm emits the modifier but only the group rows are read back.

GROUPING SETS

GroupByGroupingSets(...) groups by an explicit list of subsets. The first argument declares the full column list and each additional argument is a set of 0-based column indices; an empty set is the grand total:

var rows = dataContext.From<IComplexEntity>()
    .GroupByGroupingSets(e => new { e.Int, e.Boolean },
        new[] { 0, 1 },
        new[] { 0 },
        Array.Empty<int>())
    .Select(e => new { e.Int, e.Boolean, count = SqlFunctions.Sql.count() })
    .ToList();

select nullableint as 'Int', b as 'Boolean', count(*)
 from complex_entity group by grouping sets ((nullableint, b), (nullableint), ())

Grouping sets are available on SQL Server, PostgreSQL, SQLite and ClickHouse (SupportsGroupingSets); MySQL/MariaDB and the in-memory provider throw NotSupportedException. An out-of-range index throws BuildSqlCommandException.

`DISTINCT ON` — one row per key

PostgreSQL's DISTINCT ON is the row-picking counterpart of grouping: instead of collapsing each key into aggregates, it keys the rows the way GROUP BY does but keeps one representative row per key, chosen by ORDER BY. Use DistinctOn instead of GroupBy when you need the row itself rather than an aggregate over it:

var rows = dataContext.From<IComplexEntity>()
    .DistinctOn(e => e.String)
    .OrderBy(e => e.String)
    .Select(e => new { e.Id, e.String })
    .ToList();

select distinct on (somestring) id, somestring
 from complex_entity order by somestring

The key may be an anonymous type to key on several columns; the leading ORDER BY expressions must match it. DistinctOn cannot be combined with Distinct, and only PostgreSQL implements it — every other provider rejects it at SQL build time. See SELECT DISTINCT for the full surface.

Keeping the extreme row (`SelectWhereMax` / `SelectWhereMin`)

Operator names. The built-in operators are SelectWhereMinOld / SelectWhereMaxOld; they cover the compatibility path, including the in-memory provider. SelectWhereMin / SelectWhereMax from the extensions package are built on the public query-component layer.

SelectWhereMax and SelectWhereMin keep the row (or rows) whose value under a selector is the greatest or smallest in the source. They are a row filter, not an aggregate: a surviving row keeps every column.

Global vs per-group. Without a group key the extremum is taken over the whole source; with a group key the extreme row(s) are selected within each distinct group. A null key is a group of its own.

// Global: the single row with the greatest score.
var top = dataContext.From<ExtremaEntity>()
    .SelectWhereMax(e => e.Score)
    .ToList();

// Per group: one extreme row per category.
var perGroup = dataContext.From<ExtremaEntity>()
    .SelectWhereMax(e => e.Score, ExtremeRowTies.One, e => e.Category)
    .ToList();

-- PostgreSQL
-- global: an eligible integral key renders the native ORDER BY ... LIMIT 1
select t1.id, t1.score, t1.category, t1.label
 from (select *
 from extrema_entity
 where score is not null order by "score" desc limit 1) as "t1"

-- per group: a string group key is not native-eligible, so the portable window lowering is kept
select t1.id, t1.score, t1.category, t1.label
 from (select *, row_number() over (partition by category order by score desc) as "__nextorm_rn"
 from extrema_entity
 where score is not null) as "t1"
 where t1."__nextorm_rn" = 1

ExtremeRowTies controls ties. One (the default) keeps a single row — when values tie the provider picks one — while All keeps every row tied on the extreme value. Ties are decided only by the selector, so an All result contains all of them and a One result exactly one arbitrary member.

Projection form. The SelectWhereMax / SelectWhereMin overloads take a projection and apply it to each surviving row, returning a typed query command:

var projected = dataContext.From<ExtremaEntity>()
    .SelectWhereMax(e => e.Score, e => new { e.Id, e.Label })
    .ToList();

-- PostgreSQL
select t1.id, t1.label
 from (select *
 from extrema_entity
 where score is not null order by "score" desc limit 1) as "t1"

Mapped source. The projection-less overloads return whole entity rows, so their source must be a mapped entity type (From<TEntity>(), or another source that carries entity metadata). A name-addressed raw source such as From("orders") has no mapped columns to materialise, so preparation rejects it with BuildSqlCommandException ("SelectWhereMax/SelectWhereMin requires a mapped entity source.") instead of a misleading projection error. For a raw named source use the projected overloads above — the projection supplies the result shape — wherever the dialect supports the operation.

NULL semantics. null comparison values are ignored, so they never win the extremum and never appear in a result. A group whose comparison value is always null yields no rows at all, and the global form returns nothing when every value is null.

How the request is lowered. SelectWhereMax/SelectWhereMin are SQL-side syntactic sugar: the logical request is lowered inside the shared query pipeline, and the shape depends on the tie policy.

One lowers to a window-function derived table — row_number() over (partition by <group> order by <value> [desc]) — filtered to 1, with the source condition ANDed with an is not null filter on the value. On PostgreSQL and ClickHouse a native-eligible One request is rendered natively instead, automatically, from the provider and the query form — see Native extreme-row strategies — and every other provider or shape keeps the portable window.

All lowers to an aggregate form. It never uses a ranking window: no rank(), no row_number() and no window over (...) appears in an All statement, with one exception — a floating-point value key on ClickHouse keeps the ranking window, because that server's max()/min() treat NaN as the extreme value while its ORDER BY ranks NaN last, and the ranking window is the reference the contract is defined by. Per group it is GROUP BY <group> selecting the extreme value (plus the null-safe group key) and joined back to the source; globally it is a single scalar aggregate subquery joined back; a composite key is resolved component by component, lexicographically — each component's extremum is taken over the rows that already match the selected prefix — so (9, 2) beats (8, 100).

-- All, global: one scalar aggregate, joined back to the source
select t1.id, t1.score, t1.category, t1.label
 from extrema_entity as "t1"
 join (select max(score) as "__nextorm_ext_1_0"
 from extrema_entity) as "t2" on t1.score = t2."__nextorm_ext_1_0"

-- All, per group: the null-safe group key and the extreme value, grouped and joined back
select t1.id, t1.score, t1.category, t1.label
 from extrema_entity as "t1"
 join (select category as "__nextorm_ext_1_1", max(score) as "__nextorm_ext_1_0"
 from extrema_entity
 group by category) as "t2" on ((t1.category = t2."__nextorm_ext_1_1" or (t1.category is null and t2."__nextorm_ext_1_1" is null)) and t1.score = t2."__nextorm_ext_1_0")

-- One, per group (portable): rank the source rows, then keep rank 1
select t1.id, t1.score, t1.category, t1.label
 from (select *, row_number() over (partition by category order by score desc) as "__nextorm_rn"
 from extrema_entity
 where score is not null) as "t1"
 where t1."__nextorm_rn" = 1

Both shapes keep the caller's source condition and the null semantics: a null comparison value never wins the extremum. The window form filters with is not null before ranking; the aggregate form relies on the aggregate and the key comparison discarding null, and renders that same is not null filter inside the aggregate when the key is nullable:

-- All, global, nullable key: the aggregate filters nulls itself
select t1.id, t1.score, t1.category
 from extrema_nullable as "t1"
 join (select max(score) as "__nextorm_ext_1_0"
 from extrema_nullable
 where score is not null) as "t2" on t1.score = t2."__nextorm_ext_1_0"

Both shapes are produced by the same pipeline step, so the result contract above — whole-entity rows, the projection overloads, the mapped-source requirement and the null semantics — is identical for every tie policy and every SQL provider (SQLite, SQL Server, PostgreSQL, MySQL, MariaDB and ClickHouse). The in-memory provider evaluates the same contract directly over its own enumerator and renders no SQL at all.

The request is rejected at SQL build time when combined with another row-shaping modifier the derived-table lowering cannot express: DistinctOn, GroupBy, a join, Having, named windows, LimitBy, ArrayJoin, PreWhere, paging, a set operation or a non-physical source.

Aggregates without grouping

An aggregate over the whole table is a projection without GroupBy:

var count = dataContext.From<ISimpleEntity>().Select(e => SqlFunctions.Sql.count()).First();
var sum   = dataContext.From<ISimpleEntity>().Select(e => SqlFunctions.Sql.sum(e.Id)).First();
var avg   = dataContext.From<ISimpleEntity>().Select(e => SqlFunctions.Sql.avg(e.Id)).First();

select count(*)
 from simple_entity

Output:

Count
10

select sum(id)
 from simple_entity

Output:

Sum
55

avg returns a fractional nullable result for every numeric input (see AVG result types and argument policy), so [1, 2] is 1.5 on every provider, SQL Server included. Every aggregate also has an entity terminal that forwards to the same function: Sum, Avg, Min and Max are equivalent to Select(...) followed by First(), and Count() to Select(e => SqlFunctions.Sql.count()).First():

var sum   = dataContext.From<ISimpleEntity>().Sum(e => e.Id);            // SqlFunctions.Sql.sum
var avg   = dataContext.From<ISimpleEntity>().Avg(e => e.Id);            // SqlFunctions.Sql.avg
var min   = dataContext.From<ISimpleEntity>().Min(e => e.Id);            // SqlFunctions.Sql.min
var max   = await dataContext.From<ISimpleEntity>().MaxAsync(e => e.Id); // SqlFunctions.Sql.max
var count = dataContext.From<ISimpleEntity>().Count();                   // SqlFunctions.Sql.count

Each terminal has a synchronous and an ...Async overload (plus a parameterised one); Count() is covered in more detail in the Counting section below.

Counting

var all       = dataContext.From<ISimpleEntity>().Select(e => SqlFunctions.Sql.count()).First();             // count(*)
var nonNull   = dataContext.From<IComplexEntity>().Select(e => SqlFunctions.Sql.count(e.Int)).First();       // count(nullableint)
var distinct  = dataContext.From<IComplexEntity>().Select(e => SqlFunctions.Sql.count_distinct(e.Int)).First();
var big       = dataContext.From<ISimpleEntity>().Select(e => SqlFunctions.Sql.count_big()).First();         // long
var bigDist   = dataContext.From<IComplexEntity>().Select(e => SqlFunctions.Sql.count_big_distinct(e.Int)).First();

select count(*)
 from simple_entity
select count(nullableint)
 from complex_entity
select count(distinct nullableint)
 from complex_entity

Output:

Count
10

Output:

Count
2

Output:

Count
1

count_big() returns a 64-bit count. SQL Server has a distinct count_big function and emits count_big(...); SQLite and PostgreSQL already return a 64-bit integer from count(...), so the dialect emits count(...) for both. ClickHouse count aggregates return an unsigned UInt64, which the row reader cannot materialise, so the dialect casts count/count_distinct (and the filtered countIf) to toInt32(...) and count_big/count_big_distinct to toInt64(...).

The builder-level shortcut EntityBuilder<T>.Count() is equivalent to Select(e => SqlFunctions.Sql.count()) followed by First:

var count = dataContext.From<ISimpleEntity>().Count();

min / max / avg / sum

var min = dataContext.From<ISimpleEntity>().Select(e => SqlFunctions.Sql.min(e.Id)).First();
var max = dataContext.From<ISimpleEntity>().Select(e => SqlFunctions.Sql.max(e.Id)).First();
var avg = dataContext.From<ISimpleEntity>().Select(e => SqlFunctions.Sql.avg(e.Id)).First();
var sum = dataContext.From<ISimpleEntity>().Select(e => SqlFunctions.Sql.sum(e.Id)).First();

select min(id)
 from simple_entity
select max(id)
 from simple_entity
select avg(id)
 from simple_entity
select sum(id)
 from simple_entity

Output:

Min
1

Output:

Max
10

Output:

Avg
5.5

Output:

Sum
55

The _distinct forms add distinct:

var avgDistinct = dataContext.From<IComplexEntity>().Select(e => SqlFunctions.Sql.avg_distinct(e.Int)).First();
var sumDistinct = dataContext.From<IComplexEntity>().Select(e => SqlFunctions.Sql.sum_distinct(e.Int)).First();

select avg(distinct nullableint)
 from complex_entity
select sum(distinct nullableint)
 from complex_entity

stdev / stdevp / var / varp

var stdev  = dataContext.From<ISimpleEntity>().Select(e => SqlFunctions.Sql.stdev(e.Id)).First();
var stdevp = dataContext.From<ISimpleEntity>().Select(e => SqlFunctions.Sql.stdevp(e.Id)).First();
var var    = dataContext.From<ISimpleEntity>().Select(e => SqlFunctions.Sql.var(e.Id)).First();
var varp   = dataContext.From<ISimpleEntity>().Select(e => SqlFunctions.Sql.varp(e.Id)).First();

select stdev(id)
 from simple_entity
select stdevp(id)
 from simple_entity
select var(id)
 from simple_entity
select varp(id)
 from simple_entity

Output:

Stdev
3

Output:

Stdevp
3

Output:

Var
9

Output:

Varp
8

stdev is the sample standard deviation, stdevp the population one; var/varp are the matching variances. Each has a _distinct form (stdev_distinct, stdevp_distinct, var_distinct, varp_distinct) and an entity terminal (Stdev, Stdevp, Var, Varp) with a synchronous, ...Async and parameterised overload:

var sample = dataContext.From<ISimpleEntity>().Stdev(e => e.Id);
var asyncSample = await dataContext.From<ISimpleEntity>().VarAsync(e => e.Id);
var sumAfter5 = dataContext.From<ISimpleEntity>()
    .Where(e => e.Id > SqlFunctions.Parameter<int>(0))
    .Sum(e => e.Id, 5);

Projecting through double avoids the lossy integer conversion:

var variance = dataContext.From<ISimpleEntity>().Select(x => SqlFunctions.Sql.varp((double)x.Id)).First();

Nullable results and cardinality

The four variability families are structurally nullable: Stdev, Stdevp, Var and Varp, and the eight Sql.stdev*/Sql.var* facade forms, return T?. Cardinality counts the contributing non-null values after DISTINCT:

Contributing values	`Stdev` / `Var` (sample)	`Stdevp` / `Varp` (population)
0	`null`	`null`
1	`null`	`0.0`
2 or more	the established formula	the established formula

A defined zero stays zero: two or more identical values fold to 0.0, and a one-row population result is 0.0. Only an undefined result is null — it is never replaced by an invented zero.

The result is a real Nullable<T>: keep it (double?) or unwrap it explicitly. Assigning the terminal to a non-nullable int/double does not compile. Use ?? 0 only where zero genuinely means "no spread": it substitutes a zero for the undefined case.

Boolean, bitwise, statistical and ordered-set aggregates

Beyond count/min/max/sum/avg/stdev/var, Sql exposes several aggregate families, with the provider-only ones on Postgres (boolean, bitwise, regression and ordered-set) and ClickHouse (arg_min/arg_max, the distinct-count uniq/uniq_exact/uniq_combined/uniq_hll12, the parameterised quantile family quantile/quantile_exact/quantile_timing/quantiles/median, the most-frequent top_k/top_k_weighted, and the sequence/funnel window_funnel/sequence_match/retention). Each family is gated by its own dialect capability; PostgreSQL and ClickHouse opt into different subsets.

Family	C#	SQL	Capability	Providers
Boolean	`SqlFunctions.Postgres.bool_and(x)`, `bool_or(x)`, `every(x)`	`bool_and(x)`, ...	`SupportsBooleanAggregates`	PostgreSQL
Bitwise	`SqlFunctions.Postgres.bit_and(x)`, `bit_or(x)`, `bit_xor(x)`	`bit_and(x)` … / `groupBitAnd(x)` …	`SupportsBitAggregates`	PostgreSQL, ClickHouse
Statistical	`SqlFunctions.Sql.corr(y, x)`, `covar_pop(y, x)`, `covar_samp(y, x)`	`corr(y, x)`, ... / `covarPop(y, x)`, ...	`SupportsStatisticalAggregates`	PostgreSQL, ClickHouse
Regression	`SqlFunctions.Postgres.regr_slope(y, x)`, `regr_intercept(y, x)`, `regr_r2(y, x)`, `regr_count(y, x)`, `regr_avgx(y, x)`, `regr_avgy(y, x)`	`regr_slope(y, x)`, ...	`SupportsRegressionAggregates`	PostgreSQL
ArgMin/ArgMax	`SqlFunctions.ClickHouse.arg_min(value, by)`, `arg_max(value, by)`	`argMin(value, by)`, `argMax(value, by)`	`SupportsArgMinMax`	ClickHouse
Distinct count	`SqlFunctions.ClickHouse.uniq(x)`, `uniq_exact(x)`, `uniq_combined(x)`, `uniq_hll12(x)`	`toInt64(uniq(x))`, `toInt64(uniqExact(x))`, ...	`UniqAggregates`	ClickHouse
Quantile / median	`SqlFunctions.ClickHouse.quantile(0.5, x)`, `quantile_exact(0.9, x)`, `quantile_timing(0.5, x)`, `quantiles(new[] { 0.25, 0.5, 0.75 }, x)`, `median(x)`	`toFloat64(quantile(0.5)(x))`, `quantiles(0.25, 0.5, 0.75)(x)`, `toFloat64(median(x))`, ...	`QuantileAggregates`	ClickHouse
Most frequent (top-K)	`SqlFunctions.ClickHouse.top_k(3, x)`, `top_k_weighted(2, x, w)`	`topK(3)(x)`, `topKWeighted(2)(x, w)`	`TopKAggregates`	ClickHouse
Arbitrary value	`SqlFunctions.Sql.any_agg(x)`	`ANY_VALUE(x)` / `any(x)`	`SupportsAnyValueAggregate`	MySQL, ClickHouse
Last row	`SqlFunctions.ClickHouse.any_last(x)`	`anyLast(x)`	`SupportsAnyLastAggregate`	ClickHouse
Filtered aggregate	`SqlFunctions.Sql.count(() => p)`, `sum(x, () => p)`, `avg(x, () => p)`, `min(x, () => p)`, `max(x, () => p)`	`count(*) filter (where p)`, ... / `countIf(p)`, `sumIf(x, p)`, ...	`AggregateFilterStyle`	PostgreSQL (9.4+), SQLite (ANSI `FILTER`), ClickHouse (`-If`)
Sequence / funnel	`SqlFunctions.ClickHouse.window_funnel(window, ts, c1, c2)` , `sequence_match(pattern, ts, c1, c2)`, `retention(c1, c2)`	`toInt32(windowFunnel(window)(ts, c1, c2))`, `toInt32(sequenceMatch(pattern)(ts, c1, c2))`, `retention(c1, c2)`	`SequenceAggregates`	ClickHouse
Ordered-set	`SqlFunctions.Postgres.percentile_cont(fraction, () => x)`, `percentile_disc(fraction, () => x)`, `median(() => x)`, `mode(() => x)`	`percentile_cont(f) within group (order by x)`, `percentile_cont(0.5) within group (order by x)`, ...	`SupportsOrderedAggregates` (`mode`), `SupportsOrderedPercentiles` (percentiles, `median`)	PostgreSQL

MariaDB does not support any_agg: it has no ANY_VALUE in 10.4–12.x (the SQL-2023 T626 feature is still pending, targeted for 13.2), so SupportsAnyValueAggregate is false and the call is rejected with NotSupportedException.

var stats = dataContext.From<IComplexEntity>()
    .Select(e => new
    {
        AllTrue = SqlFunctions.Postgres.bool_and(e.Boolean),
        Xor = SqlFunctions.Postgres.bit_xor(e.Id),
        Correlation = SqlFunctions.Sql.corr(e.Id, e.Int),
        Median = SqlFunctions.Postgres.percentile_cont(0.5, () => e.Id)
    })
    .First();

select bool_and(b), bit_xor(id), corr(id, nullableint), percentile_cont(0.5) within group (order by id)
 from complex_entity

On ClickHouse the same families render with their own spellings — groupBitAnd, covarPop, argMin/argMax — while the boolean aggregates and the regr_* family are not available (ClickHouse has neither bool_and nor regr_*, and the dialect rejects them with NotSupportedException):

var rows = dataContext.From<IComplexEntity>()
    .Select(e => new
    {
        Bits = SqlFunctions.Postgres.bit_and(e.Id),
        Cov = SqlFunctions.Sql.covar_pop(e.Id, e.Int),
        FirstByMax = SqlFunctions.ClickHouse.arg_max(e.String, e.Id)
    })
    .First();

-- ClickHouse
select groupBitAnd(id), covarPop(id, nullableint), argMax(somestring, id)
 from complex_entity

The ordered-set aggregates take the ordering key as a quoted lambda that closes over the query parameter; the key becomes order by <key>. Calling one on a provider without the capability throws NotSupportedException.

On PostgreSQL the median is not a server function: SqlFunctions.Postgres.median(() => x) renders percentile_cont(0.5) within group (order by x) and is an aggregate (one row per group), gated by SupportsOrderedPercentiles. The continuous forms interpolate and return double precision, so percentile_cont and median require a floating-point sort expression — an integer/numeric key is refused with The percentile_cont function on PostgreSQL requires a floating-point sort expression. — while percentile_disc keeps the sort expression's type. A fraction is a double in [0, 1], not a percentage; it must be finite and constant (a literal or a captured variable), and a row-dependent fraction is refused at SQL generation. The generic SqlFunctions.Postgres.percentile(fraction, () => x) is refused: it never becomes an alias of the continuous form.

The ClickHouse sequence/funnel aggregates take the conditions as inline boolean expressions (the timestamp column first). window_funnel returns the longest consecutive chain in a sliding window, sequence_match returns 1/0 for the pattern; both materialise as a CLR int (the dialect casts the native unsigned result with toInt32(...)). retention returns a UInt8 mask that the dialect normalises to the declared int[] (project it directly or use it inside another array function):

var funnel = dataContext.From<IEventEntity>()
    .Select(e => new
    {
        Level = SqlFunctions.ClickHouse.window_funnel(600, e.Timestamp, e.Event == 1, e.Event == 2),
        Matched = SqlFunctions.ClickHouse.sequence_match("(?1).*(?2)", e.Timestamp, e.Event == 1, e.Event == 2),
        Mask = SqlFunctions.ClickHouse.array_string_concat(
            SqlFunctions.ClickHouse.retention(e.Event == 1, e.Event == 2), ",")
    })
    .First();

-- ClickHouse
select toInt32(windowFunnel(600)(ts, (event = 1), (event = 2))) as `Level`,
       toInt32(sequenceMatch('(?1).*(?2)')(ts, (event = 1), (event = 2))) as `Matched`,
       arrayStringConcat(cast(retention((event = 1), (event = 2)) as Array(Int32)), ',') as `Mask`
 from event_entity

Array-returning aggregates follow the declared CLR element type, not the server-inferred one: top_k/top_k_weighted/group_array/group_uniq_array declare a nullable element type when the input is nullable (int?[] → Array(Nullable(Int32))), while retention stays int[] and array_map/range follow their own declared types (int[], long[]). Nullability describes the result type only: ClickHouse still elides NULL aggregate inputs, so group_array/group_uniq_array over a nullable column omit the nulls instead of retaining them — the declared element type is nullable, the aggregate does not keep nulls.

Filtered aggregates

An aggregate can carry a row filter by passing a predicate as an extra argument. The same API works across providers; the dialect picks the spelling through AggregateFilterStyle. PostgreSQL and SQLite render the ANSI filter (where ...) clause, ClickHouse renders its -If combinator; MySQL/MariaDB and SQL Server reject the call with NotSupportedException because they have neither. On PostgreSQL the ANSI form requires server version 9.4 or later: a dialect configured with an older Version rejects the call with NotSupportedException, while an unset version assumes 9.4+ (see PostgreSQL provider).

var rows = dataContext.From<IComplexEntity>()
    .GroupBy(e => new { e.Int })
    .Select(e => new
    {
        e.Int,
        Big = SqlFunctions.Sql.count(() => e.Id > 10L),
        Total = SqlFunctions.Sql.sum(e.Id, () => e.Boolean == true)
    })
    .ToList();

select nullableint, count(*) filter (where (id > 10)) as "Big", sum(id) filter (where (b = true)) as "Total"
 from complex_entity group by nullableint

The same query on ClickHouse renders the -If combinator instead:

-- ClickHouse
select nullableint, toInt32(countIf((id > 10))) as `Big`, sumIf(id, (b = true)) as `Total`
 from complex_entity group by nullableint

count/count_big/min/max/avg/sum and string_agg take the filter. On ClickHouse string_agg becomes arrayStringConcat(groupArrayIf(value, predicate), delimiter). MySQL/MariaDB, SQL Server and the in-memory provider have no filtered-aggregate form.

string_agg/array_agg take a filter as well; string_agg is available on PostgreSQL, SQL Server 2017+ and ClickHouse (as arrayStringConcat(groupArray(x), delimiter)), while array_agg is PostgreSQL-only (SQL Server has no array type). Both results are array columns, materialised by the row reader as a CLR T[]. ClickHouse additionally exposes its native array-returning aggregates group_array/group_uniq_array (groupArray/groupUniqArray). See Scalar functions for the full surface.

Provider differences

Provider	`count_big`	Aggregate names	Integer `AVG`	`var` / `varp`
SQLite	emits `count(*)` (already 64-bit)	kept as written; `stdev`/`var` come from the custom aggregates registered by the provider	fractional (5.5 stays 5.5)	supported through those custom aggregates
SQL Server	emits `count_big(...)`	kept as written; `stdev`/`var` are native	fractional; the argument is widened (`avg(cast(id as float))`)	native
PostgreSQL	emits `count(*)` (already 64-bit)	`stdev` → `stddev`, `stdevp` → `stddev_pop`, `var` → `variance`, `varp` → `var_pop`	fractional	native
MySQL	emits `count(*)` (already 64-bit)	`stdev` → `stddev_samp`, `stdevp` → `stddev_pop`, `var` → `var_samp`, `varp` → `var_pop`	fractional	native (`var_samp` / `var_pop`)
MariaDB	emits `count(*)` (already 64-bit)	same mapping as MySQL (`stddev_samp` / `stddev_pop` / `var_samp` / `var_pop`)	fractional	native (`var_samp` / `var_pop`)
ClickHouse	casts `count`/`count_distinct` (and the filtered `countIf`) to `toInt32(...)` and `count_big`/`count_big_distinct` to `toInt64(...)` (native `UInt64` is not materialisable)	`stdev` → `stddevSamp`, `stdevp` → `stddevPop`, `var` → `varSamp`, `varp` → `varPop`, `covar_` → `covarPop`/`covarSamp`, `bit_` → `groupBit*`, an aggregate filter → the `<fn>If` combinator (`sumIf`, `countIf`)	fractional	`varSamp` / `varPop`
In-memory	not covered by the in-memory test suite	not covered	fractional (`double?` for integral inputs)	not covered

Group ordering is not defined by SQL; assert by group key, not by position. PostgreSQL and SQLite differ on NULL ordering (PostgreSQL first, SQLite last).

GROUP BY ROLLUP (...)/CUBE (...) is emitted in the ANSI form by SQL Server, PostgreSQL and SQLite, and as the trailing ... WITH ROLLUP/WITH CUBE by MySQL/MariaDB and ClickHouse. MySQL/MariaDB have no CUBE, so GroupByCube throws there; the in-memory provider rejects both modifiers. GROUP BY GROUPING SETS (...) is available on SQL Server, PostgreSQL, SQLite and ClickHouse, but not on MySQL/MariaDB or the in-memory provider. WITH TOTALS is ClickHouse-only; the in-memory provider rejects it as well as ROLLUP/CUBE.

Table of Contents

Grouping and aggregates

Overview

AVG result types and argument policy

GroupBy

Having

Grouping with sort and limit

Grouping inferred from the projection: SelectGroup

SelectGroupHaving

ROLLUP and CUBE

WITH TOTALS

GROUPING SETS

`DISTINCT ON` — one row per key

Keeping the extreme row (`SelectWhereMax` / `SelectWhereMin`)

Aggregates without grouping

Counting

min / max / avg / sum

stdev / stdevp / var / varp

Nullable results and cardinality

Boolean, bitwise, statistical and ordered-set aggregates

Filtered aggregates

Provider differences

See also

Table of Contents

Grouping and aggregates

Overview

AVG result types and argument policy

GroupBy

Having

Grouping with sort and limit

Grouping inferred from the projection: SelectGroup

SelectGroupHaving

ROLLUP and CUBE

WITH TOTALS

GROUPING SETS

DISTINCT ON — one row per key

Keeping the extreme row (SelectWhereMax / SelectWhereMin)

Aggregates without grouping

Counting

min / max / avg / sum

stdev / stdevp / var / varp

Nullable results and cardinality

Boolean, bitwise, statistical and ordered-set aggregates

Filtered aggregates

Provider differences

See also

`DISTINCT ON` — one row per key

Keeping the extreme row (`SelectWhereMax` / `SelectWhereMin`)