Skip to content

SQL aggregates ​

Use aggregate expressions in the SELECT list of db.query<T>. Declare each result field in T with the type shown below, and give every expression an explicit AS alias matching that field.

ExpressionArgumentResult field
COUNT(*) AS totalAll matching rowstotal: i64
COUNT(column) AS totalNonempty values in the columntotal: i64
SUM(column) AS sumi64 or f64 columnsum: i64? or sum: f64?
AVG(column) AS averagei64 or f64 columnaverage: f64?
MIN(column) AS minimumi64, f64, or string columnOptional column type
MAX(column) AS maximumi64, f64, or string columnOptional column type

COUNT(*) counts rows; COUNT(column) counts rows with a value in that column. Both return 0 when no values match. The other aggregates return an empty optional in that case, so use ?? or narrow the value before using it. An aggregate query without GROUP BY returns one row even when no source rows match.

Example ​

This example assumes the local schema snapshot contains db_examples(id: i64) and the runtime database has the same table.

tyrus
import { DbError, db } from "tyrus:db";

interface CountRow {
    total: i64;
}

interface StatsRow {
    sum: i64?;
    average: f64?;
    minimum: i64?;
    maximum: i64?;
}

interface GroupRow {
    groupId: i64;
    total: i64;
}

async function countExamples(): Promise<Result<CountRow[], DbError>> {
    const rows = await db.query<CountRow>("SELECT COUNT(*) AS total FROM db_examples", []) onfail {
        return Err(data);
    };
    return Ok(rows);
}

async function readStats(): Promise<Result<StatsRow[], DbError>> {
    const rows = await db.query<StatsRow>("""
        SELECT SUM(id) AS sum,
               AVG(id) AS average,
               MIN(id) AS minimum,
               MAX(id) AS maximum
        FROM db_examples
    """, []) onfail {
        return Err(data);
    };
    return Ok(rows);
}

async function listGroups(): Promise<Result<GroupRow[], DbError>> {
    const rows = await db.query<GroupRow>("SELECT id AS groupId, COUNT(*) AS total FROM db_examples GROUP BY groupId ORDER BY total DESC", []) onfail {
        return Err(data);
    };
    return Ok(rows);
}

You can combine multiple aggregates in one SELECT.

GROUP BY ​

Use GROUP BY to combine plain columns and aggregates. Every selected plain column must appear in the grouping list, either by its column name, qualified column name, or explicit SELECT alias. SELECT * is not accepted with GROUP BY; list result columns explicitly. HAVING filters groups after aggregation and may refer to grouped columns or aggregate aliases. Bind values through ? parameters. ORDER BY may refer to a grouped column or an aggregate alias. The result contains one row per group, or an empty array if no groups match.

This example returns one row per id value:

tyrus
import { DbError, db } from "tyrus:db";

interface GroupRow {
    groupId: i64;
    total: i64;
}

async function listGroups(): Promise<Result<GroupRow[], DbError>> {
    const minimumTotal: i64 = 1;
    const rows = await db.query<GroupRow>("""
        SELECT id AS groupId, COUNT(*) AS total
        FROM db_examples
        GROUP BY groupId
        HAVING total > ?
        ORDER BY total DESC
    """, [minimumTotal]) onfail {
        return Err(data);
    };
    return Ok(rows);
}

SQL must be a string literal, and dynamic values belong in the separate parameter array.