Aggregates

Count, sum, average, min and max without loading rows into memory.
The database does the arithmetic; you get the number back.

Counting

count.ts

await repo.count();                     // all rows
await repo.count({ status: "active" });  // matching rows
await repo.countDistinct("email");       // distinct values in a column

Soft-deleted rows are excluded from all three, the same way they are excluded from a normal find.

aggregate()

This is the single entry point for sum, avg, min, max and count. Each key takes an array of columns and every result comes back in one flat object, keyed <fn>_<column>.

aggregate.ts

const stats = await repo.aggregate({
  count: ["*", "status"],
  sum: ["amount"],
  avg: ["amount"],
  min: ["amount"],
  max: ["amount"],
});

// {
//   count_all: 42,
//   count_status: 42,
//   sum_amount: 100,
//   avg_amount: 33.3,
//   min_amount: 10,
//   max_amount: 60
// }

Count a star with the string "*"; the key becomes count_all rather than the unusable count_*. Every aggregate runs in a single query, so adding more columns costs nothing extra in round trips.

On the Query Builder

The builder offers each function directly. All five are chainable and return a QueryBuilder, so you run them with execute() and get an array of rows back:

builder.ts

const rows = await repo.find().sum("amount", "total").execute(db.client);
// [{ total: 100 }]

const totals = await repo
  .find()
  .where("status = ?", "paid")
  .count("id", "orders")
  .sum("amount", "revenue")
  .avg("amount", "average")
  .execute(db.client);
// [{ orders: 42, revenue: 100, average: 2.38 }]

Each takes an optional alias as the second argument — count, sum, avg, min and max respectively. For a bare count or existence check, skip the row fetch entirely:

exec.ts

const total = await repo.find().where("active = ?", true).countExec(db.client);
// => number

const any = await repo.find().where("email = ?", email).existsExec(db.client);
// => boolean

Getting the SQL

Any builder can be rendered to SQL without running it, which is the quickest way to check what an aggregate will actually do:

tosql.ts

const { query, params } = repo
  .find()
  .where("status = ?", "paid")
  .sum("amount", "revenue")
  .toSQL();

console.log(query);   // SELECT SUM(amount) AS revenue FROM orders WHERE status = ?
console.log(params);  // ["paid"]

API Reference

api.ts

// Repository
count(conditions?: Partial<T>): Promise<number>
countDistinct(column: string): Promise<number>
aggregate(options: {
  count?: string | string[];
  sum?: string[];
  avg?: string[];
  min?: string[];
  max?: string[];
}): Promise<Record<string, any>>

// QueryBuilder - all chainable
count(column = "*", alias = "count"): QueryBuilder<T>
sum(column: string, alias = "sum"): QueryBuilder<T>
avg(column: string, alias = "avg"): QueryBuilder<T>
min(column: string, alias = "min"): QueryBuilder<T>
max(column: string, alias = "max"): QueryBuilder<T>
countExec(client: DBClient): Promise<number>
existsExec(client: DBClient): Promise<boolean>

sum/avg/min/max are builder-only

There are no repo.sum() / repo.avg() methods. On a repository use aggregate(); on a builder use the chainable methods. count and countDistinct exist on both.