groupBy
Group records and compute aggregates per group
Group records by one or more fields and compute aggregates per group:
const byRole = await client.user.groupBy({
by: ["role"],
_count: true,
});
// Result:
// [
// { role: "USER", _count: 100 },
// { role: "ADMIN", _count: 5 },
// ]
With aggregates
const stats = await client.post.groupBy({
by: ["authorId"],
_count: true,
_avg: { views: true },
_sum: { views: true },
});
// [
// { authorId: "user_1", _count: 10, _avg: { views: 50 }, _sum: { views: 500 } },
// { authorId: "user_2", _count: 5, _avg: { views: 100 }, _sum: { views: 500 } },
// ]
With filtering
Use where to filter rows before grouping, and having to filter groups after:
const stats = await client.post.groupBy({
by: ["authorId"],
where: { published: true },
_count: true,
having: {
authorId: { _count: { gt: 5 } }, // Only authors with 5+ posts
},
orderBy: { _count: { _all: "desc" } },
take: 10,
});
By a time step
A date or dateTime column can be grouped by period: write the member as
{ column: step }, one column per member, beside plain columns if you like.
The steps are day, week, month, quarter and year.
const monthly = await client.facture.groupBy({
by: ["statut", { date: "month" }],
where: { date: { gte: "2026-01-01T00:00:00-05:00" } },
_sum: { montant: true },
_count: true,
orderBy: { date: "asc" },
timeZone: "America/Montreal",
});
// [
// { statut: "payee", date: "2026-01-01", _sum: { montant: 12400 }, _count: 9 },
// { statut: "payee", date: "2026-02-01", _sum: { montant: 8100 }, _count: 6 },
// …
// ]
The grouping happens in the database, in the one statement.
- What comes back. Under the column’s name, the first day of the period
as a
YYYY-MM-DDstring (typedstring, notDate). A week starts on Monday. A nullable column keeps itsnullgroup. A period that begins outside the years 1 to 9999 keeps the ISO astronomical year:0000-01-01for year zero,-0001-12-27for the week of 0000-01-01. timeZone. An IANA name ("America/Montreal","Asia/Kolkata","UTC"), checked before anything is sent: an offset ("+05:00","UTC+5","Etc/GMT+5", whose sign reads backwards) or an abbreviation ("EST") is refused. The check uses the zone list of the runtime that makes the call; a database whose time zone data lacks a name (a retired link, a zone newer than its server) still answers with an error. Without it, periods are taken in UTC. The database session’s own zone never counts. A column that stores an instant (s.dateTime(), PostgreSQLtimestamptz) is placed by its day in that zone, daylight-saving changes included. As.date()column, or a date and time with no zone (s.dateTime().withoutTimezone(),timestamp), is placed as written:timeZoneis accepted and changes nothing for it.orderBy. A column grouped by a step counts as grouped: ordering by it orders by period.having. Aggregates over the column work as usual (having: { date: { _count: { gt: 1 } } }); comparing the column itself is refused for now. Filter the rows withwhereinstead.
Options
await client.post.groupBy({
by: ["field1", { date: "month" }], // Group by fields, or by period
timeZone: "America/Montreal", // Where periods begin (default UTC)
where: { ... }, // Filter before grouping
having: { ... }, // Filter after grouping
orderBy: { ... }, // Sort groups
take: 10, // Limit groups
skip: 0, // Offset groups
_count: true | { ... },
_avg: { ... },
_sum: { ... },
_min: { ... },
_max: { ... },
});
Examples
Top authors
async function getTopAuthors(limit = 10) {
return client.post.groupBy({
by: ["authorId"],
where: { published: true },
_count: true,
_sum: { views: true },
orderBy: { _sum: { views: "desc" } },
take: limit,
});
}