Mise Aggregation

Aggregation

Use aggregate expressions and GroupBy to summarize rows. Where filters input rows before grouping; Having filters the resulting groups.

Count courses by campus

// CampusCourseCountRow.cs
[Mise]
public sealed partial record CampusCourseCountRow
{
    [Column("campus_id")]
    public int? CampusId { get; init; }

    [Column("course_count")]
    public required long CourseCount { get; init; }
}

var query = new PostgreSqlQueryBuilder()
    .Select($"{CourseTbl.CampusIdCol:raw}")
    .Select($"COUNT(*) AS course_count")
    .From($"{CourseTbl.Table:raw}")
    .Where($"{CourseTbl.CapacityCol:raw} >= {minimumCapacity}")
    .GroupBy($"{CourseTbl.CampusIdCol:raw}")
    .Having($"COUNT(*) >= {minimumCourses}")
    .OrderBy($"course_count DESC");

var counts = await reader.ListAsync<CampusCourseCountRow>(query, cancellationToken);

This groups qualifying courses by campus and keeps campuses with at least minimumCourses matching courses. The null campus ID represents courses without a campus. PostgreSQL's COUNT(*) produces a 64-bit integer, so the row maps it to long.

Multiple grouping keys

Repeated GroupBy calls add comma-separated expressions. Repeated Having calls combine predicates with AND. Include each nonaggregate selected expression in the grouping when your database requires it.

The builder accepts SQL expressions; it does not infer GROUP BY keys from the projection or check aggregate correctness. The database validates those relationships when executing the SQL.

Aggregate nulls

COUNT(*) counts rows. COUNT(column) counts non-null values in that column. Aggregates such as SUM can return SQL NULL when no values contribute. Map nullable aggregate outputs to nullable properties or use an explicit SQL expression such as COALESCE when the application needs a default.

For a single total, use a scalar query:

var totalQuery = new PostgreSqlQueryBuilder()
    .Select($"COUNT(*)")
    .From($"{CourseTbl.Table:raw}");

Result<long?> total = await reader.ScalarAsync<long?>(totalQuery, cancellationToken);

Scalar methods cast the provider's value to the requested type; choose a type matching the database expression. For SQL Server, COUNT_BIG(*) is the corresponding 64-bit count expression. See Database dialects and Readers.