Aggregates

Aggregate methods are available via model.advanced() which returns anAdvancedQueryBuilder<T>. This extends the base query builder withcountAggregate, sum, avg, min,max, groupBy, having, distinct, and window.

countAggregate

Adds COUNT(*) AS count to the SELECT clause. Different fromModelAPI.count() which runs a standalone COUNT query returning a scalar.

// .countAggregate() — adds COUNT(*) AS count to SELECT
// Different from ModelAPI.count() which returns a scalar number

const result = await Post.advanced()
  .countAggregate()
  .groupBy('userId')
  .get();

// result[0].count — number of posts per user
result.forEach(row => {
  console.log(`User ${row.userId}: ${row.count} posts`);
});

sum / avg

// .sum(column) / .avg(column)
// Returns rows with the aggregate value added

const totals = await Order.advanced()
  .sum('amount')
  .groupBy('userId')
  .get();

// totals[0].sum_amount

const avgRatings = await Review.advanced()
  .avg('rating')
  .groupBy('productId')
  .get();

// avgRatings[0].avg_rating

min / max

// .min(column) / .max(column)

const extremes = await Product.advanced()
  .min('price')
  .max('price')
  .groupBy('categoryId')
  .get();

// extremes[0].min_price
// extremes[0].max_price

groupBy

// .groupBy(...columns)
const postCounts = await Post.advanced()
  .select('userId')
  .countAggregate()
  .groupBy('userId')
  .orderBy('count', 'DESC')
  .get();

having

// .having(rawSql, params?)
// Filters groups after aggregation

const activeUsers = await Post.advanced()
  .select('userId')
  .countAggregate()
  .groupBy('userId')
  .having('COUNT(*) > ?', [5])  // only users with more than 5 posts
  .get();

distinct

// .distinct(...columns) — SELECT DISTINCT

const roles = await User.advanced()
  .distinct('role')
  .get();
// Returns unique role values

const pairs = await Post.advanced()
  .distinct('userId', 'categoryId')
  .get();

window functions

.window(fn, over) adds a window function expression to the SELECT. The result appears as window_result on each row. Supported by Postgres and MySQL; SQLite has limited window support.

// .window(fn, over) — window function expression
// Added to SELECT as: fn OVER (over) AS window_result

const ranked = await Post.advanced()
  .select('id', 'title', 'userId', 'createdAt')
  .window(
    'ROW_NUMBER()',
    'PARTITION BY userId ORDER BY createdAt DESC'
  )
  .get();

// ranked[0].window_result — row number within each user partition

// Running total example
const runningTotal = await Order.advanced()
  .select('id', 'userId', 'amount', 'createdAt')
  .window(
    'SUM(amount)',
    'PARTITION BY userId ORDER BY createdAt'
  )
  .get();

PreviousPreloads NextSubqueries