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

Options

await client.post.groupBy({
  by: ["field1", "field2"],  // Group by fields
  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,
  });
}