---
title: groupBy
description: Group records and compute aggregates per group
icon: group
---

Group records by one or more fields and compute aggregates per group:

```ts
const byRole = await client.user.groupBy({
  by: ["role"],
  _count: true,
});

// Result:
// [
//   { role: "USER", _count: 100 },
//   { role: "ADMIN", _count: 5 },
// ]
```

## With aggregates

```ts
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:

```ts
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`.

```ts
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.

:::warning
`timeZone` does not change `where`. The bounds in `where` are instants: write
them with their offset (`"2026-01-01T00:00:00-05:00"`), or the first and last
periods will miss rows or take some from their neighbours.
:::

:::info
Grouping by a time step is supported on PostgreSQL, on every driver (`pg`,
`postgres.js`, Neon HTTP, PGlite, Bun SQL). The other databases refuse such a
call with a `FeatureNotSupportedError` before any statement is sent. Periods
with no rows are not filled in.
:::

## Options

```ts
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: { ... },
});
```

:::info
Every `orderBy` field must either appear in `by` or be an aggregate key (`_count`, `_avg`, `_sum`, `_min`, `_max`). Note the two shapes differ: `orderBy` takes the aggregate at the top level, while `having` is keyed by field name with the aggregate nested inside (`having: { authorId: { _count: { gt: 5 } } }`).

  ```ts
  await client.post.groupBy({
    by: ["authorId"],
    _sum: { views: true },
    orderBy: { _sum: { views: "desc" } },
  });
  ```
:::

## Examples

### Top authors

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