Aggregates & GroupBy

Count records, sum values, calculate averages — with GROUP BY for analytics queries. All built into the query builder, not raw SQL.

#Why aggregates?

"How many posts does each author have?" "What's the total revenue per month?" These questions require aggregation — combining multiple rows into summary values. Without aggregates, you'd either write raw SQL or fetch all rows and compute in JavaScript. Zanith lets you express these queries naturally in the builder.

#Available functions

1 line


1import { count, countDistinct, sum, avg, min, max } from 'zanith';

Function	SQL output	Use case
`count()`	`COUNT(*)`	Count all rows in each group
`count(field)`	`COUNT(field)`	Count non-null values of a field
`countDistinct(field)`	`COUNT(DISTINCT field)`	Count unique values
`sum(field)`	`SUM(field)`	Total of numeric values
`avg(field)`	`AVG(field)`	Average of numeric values
`min(field)`	`MIN(field)`	Smallest value
`max(field)`	`MAX(field)`	Largest value

#Basic example

Count posts and sum views per author — a typical analytics query:

TSposts per author

19 lines


1const stats = await db.post.query()
2  .with({ author: true })
3  .select(({ post, author }) => ({
4    authorEmail: author.email,
5    postCount: count(),
6    totalViews: sum(post.viewCount),
7    avgViews: avg(post.viewCount),
8    bestPost: max(post.viewCount),
9  }))
10  .groupBy(({ post, author }) => [post.authorId, author.email])
11  .execute();
12 
13// stats: Array<{
14//   authorEmail: string,
15//   postCount: number,
16//   totalViews: number,
17//   avgViews: number,
18//   bestPost: number
19// }>

#Simple count by category

10 lines


1// How many posts in each status?
2const statusCounts = await db.post.query()
3  .select(({ post }) => ({
4    status: post.status,
5    total: count(),
6  }))
7  .groupBy(({ post }) => [post.status])
8  .execute();
9 
10// [{ status: 'PUBLISHED', total: 42 }, { status: 'DRAFT', total: 15 }, ...]

#Count distinct

How many unique authors have published posts?

6 lines


1const result = await db.post.query()
2  .select(({ post }) => ({
3    uniqueAuthors: countDistinct(post.authorId),
4  }))
5  .where(({ post }) => post.published.eq(true))
6  .execute();

#Window functions

For ranking, running totals, and row numbering — see theWindow Functions page. Window functions compute values across rows without collapsing them, unlike GROUP BY.