Skip to content
VibORM
Esc
↑↓navigate↵open⌘Jpreview
On this page

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-DD string (typed string, not Date). A week starts on Monday. A nullable column keeps its null group. A period that begins outside the years 1 to 9999 keeps the ISO astronomical year: 0000-01-01 for year zero, -0001-12-27 for 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(), PostgreSQL timestamptz) is placed by its day in that zone, daylight-saving changes included. A s.date() column, or a date and time with no zone (s.dateTime().withoutTimezone(), timestamp), is placed as written: timeZone is 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 with where instead.

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,
  });
}

Was this page helpful?