Aggregate per group
Summarize records by a category, project, or other column.
Drizzle ORM · Module
.groupBy
.groupBy adds grouping criteria to a Drizzle select query and generates SQL GROUP BY. It is commonly used with aggregate expressions, with .having available to filter groups.
How it works
.groupBy adds SQL GROUP BY criteria to a select query. Pass columns or SQL expressions to define the grouping key, and combine aggregate selections with .having when filtering grouped results.
Syntax
.groupBy(...columns)Parameters
| Name | Type | Description |
|---|---|---|
columns | Column or SQL expression(s) | Columns or SQL expressions that determine which rows belong to the same group. |
Returns
A select query builder that emits a SQL GROUP BY clause.
Key points
- Call
.groupByafter.fromand pass one or more table columns as grouping criteria. - The builder translates the grouping call into a SQL GROUP BY clause using the selected column.
- The SQL template can be used in both HAVING and GROUP BY clauses.
- Pass a SQL template to
.groupBywhen grouping by an expression rather than a plain column. - Aggregate selections can be typed with a SQL expression and converted with
.mapWith(Number).
Common mistakes
- Use a SQL expression in
.groupBywhen the query requires grouping behavior not directly represented by the standard builder.
Example: Group rows and filter aggregates
import { sql } from 'drizzle-orm';
import { usersTable } from './schema';
const rows = await db
.select({
projectId: usersTable.projectId,
count: sql<number>`count(${usersTable.id})`.mapWith(Number),
})
.from(usersTable)
.groupBy(usersTable.projectId)
.having(sql`count(${usersTable.id}) > 300`);Groups users by project and keeps only groups containing more than 300 users.
When to use it
Filter grouped results with HAVING
Retain only groups meeting an aggregate threshold.
Group by a SQL expression
Express grouping logic that needs custom SQL.
Interview questions
QWhat does .groupBy do?
.groupBy adds a GROUP BY clause to a select query. It receives the columns or SQL expressions that define each group.
Follow-up: How can grouped results be filtered? Use .having, typically with an aggregate SQL expression such as a count condition.
QWhen should .groupBy accompany aggregate functions?
When a query selects aggregate values and ordinary columns, the ordinary selected columns need to be represented in the grouping criteria. Drizzle emits the corresponding SQL GROUP BY clause.
Follow-up: What should you do when the grouping expression is not directly available? Pass an sql template expression to .groupBy.
QHow do you use a custom expression with .groupBy?
The builder accepts SQL expressions, so sql can be used directly in a GROUP BY clause. This supports grouping behavior that needs custom SQL while retaining the query builder.
Follow-up: Can the same SQL expression be used in HAVING? Yes; the documentation demonstrates an aggregate count expression in .having.
Related
selectfromhavingsqlmapWith