# Aggregation, grouping, and summarizing (/docs/orm/v6/prisma-client/queries/aggregation-grouping-summarizing)

Location: ORM > v6 > Prisma Client > Queries > Aggregation, grouping, and summarizing

Prisma Client allows you to count records, aggregate number fields, and select distinct field values.

Aggregate [#aggregate]

Prisma Client allows you to [`aggregate`](/orm/v6/reference/prisma-client-reference#aggregate) on the **number** fields (such as `Int` and `Float`) of a model. The following query returns the average age of all users:

```ts
const aggregations = await prisma.user.aggregate({
  _avg: {
    age: true,
  },
});

console.log("Average age:" + aggregations._avg.age);
```

You can combine aggregation with filtering and ordering. For example, the following query returns the average age of users:

* Ordered by `age` ascending
* Where `email` contains `prisma.io`
* Limited to the 10 users

```ts
const aggregations = await prisma.user.aggregate({
  _avg: {
    age: true,
  },
  where: {
    email: {
      contains: "prisma.io",
    },
  },
  orderBy: {
    age: "asc",
  },
  take: 10,
});

console.log("Average age:" + aggregations._avg.age);
```

Aggregate values are nullable [#aggregate-values-are-nullable]

In [2.21.0](https://github.com/prisma/prisma/releases/tag/2.21.0) and later, aggregations on **nullable fields** can return a `number` or `null`. This excludes `count`, which always returns 0 if no records are found.

Consider the following query, where `age` is nullable in the schema:

```ts
const aggregations = await prisma.user.aggregate({
  _avg: {
    age: true,
  },
  _count: {
    age: true,
  },
});
```

```js no-copy
{
  _avg: {
    age: null
  },
  _count: {
    age: 9
  }
}
```

The query returns `{ _avg: { age: null } }` in either of the following scenarios:

* There are no users
* The value of every user's `age` field is `null`

This allows you to differentiate between the true aggregate value (which could be zero) and no data.

Group by [#group-by]

Prisma Client's [`groupBy()`](/orm/v6/reference/prisma-client-reference#groupby) allows you to **group records** by one or more field values - such as `country`, or `country` and `city` and **perform aggregations** on each group, such as finding the average age of people living in a particular city. `groupBy()` is a GA in [2.20.0](https://github.com/prisma/prisma/releases/2.20.0) and later.

The following video uses `groupBy()` to summarize total COVID-19 cases by continent:

[Watch video](https://www.youtube.com/watch?v=BdlCPdPaorY)

The following example groups all users by the `country` field and returns the total number of profile views for each country:

```ts
const groupUsers = await prisma.user.groupBy({
  by: ["country"],
  _sum: {
    profileViews: true,
  },
});
```

```js no-copy
[
  { country: "Germany", _sum: { profileViews: 126 } },
  { country: "Sweden", _sum: { profileViews: 0 } },
];
```

If you have a single element in the `by` option, you can use the following shorthand syntax to express your query:

```ts
const groupUsers = await prisma.user.groupBy({
  by: "country",
});
```

groupBy() and filtering [#groupby-and-filtering]

`groupBy()` supports two levels of filtering: `where` and `having`.

Filter records with where [#filter-records-with-where]

Use `where` to filter all records **before grouping**. The following example groups users by country and sums profile views, but only includes users where the email address contains `prisma.io`:

```ts highlight=3-7;normal
const groupUsers = await prisma.user.groupBy({
  by: ["country"],
  where: {
    // [!code highlight]
    email: {
      // [!code highlight]
      contains: "prisma.io", // [!code highlight]
    }, // [!code highlight]
  }, // [!code highlight]
  _sum: {
    profileViews: true,
  },
});
```

Filter groups with having [#filter-groups-with-having]

Use `having` to filter **entire groups** by an aggregate value such as the sum or average of a field, not individual records - for example, only return groups where the *average* `profileViews` is greater than 100:

```ts highlight=11-17;normal
const groupUsers = await prisma.user.groupBy({
  by: ["country"],
  where: {
    email: {
      contains: "prisma.io",
    },
  },
  _sum: {
    profileViews: true,
  },
  having: {
    // [!code highlight]
    profileViews: {
      // [!code highlight]
      _avg: {
        // [!code highlight]
        gt: 100, // [!code highlight]
      }, // [!code highlight]
    }, // [!code highlight]
  }, // [!code highlight]
});
```

Use case for having [#use-case-for-having]

The primary use case for `having` is to filter on aggregations. We recommend that you use `where` to reduce the size of your data set as far as possible *before* grouping, because doing so ✔ reduces the number of records the database has to return and ✔ makes use of indices.

For example, the following query groups all users that are *not* from Sweden or Ghana:

```ts highlight=4-6;normal
const fd = await prisma.user.groupBy({
  by: ["country"],
  where: {
    country: {
      // [!code highlight]
      notIn: ["Sweden", "Ghana"], // [!code highlight]
    }, // [!code highlight]
  },
  _sum: {
    profileViews: true,
  },
  having: {
    profileViews: {
      _min: {
        gte: 10,
      },
    },
  },
});
```

The following query technically achieves the same result, but excludes users from Ghana *after* grouping. This does not confer any benefit and is not recommended practice.

```ts highlight=4-6,12-14;normal
const groupUsers = await prisma.user.groupBy({
  by: ["country"],
  where: {
    country: {
      // [!code highlight]
      not: "Sweden", // [!code highlight]
    }, // [!code highlight]
  },
  _sum: {
    profileViews: true,
  },
  having: {
    country: {
      // [!code highlight]
      not: "Ghana", // [!code highlight]
    }, // [!code highlight]
    profileViews: {
      _min: {
        gte: 10,
      },
    },
  },
});
```

> **Note**: Within `having`, you can only filter on aggregate values *or* fields available in `by`.

groupBy() and ordering [#groupby-and-ordering]

The following constraints apply when you combine `groupBy()` and `orderBy`:

* You can `orderBy` fields that are present in `by`
* You can `orderBy` aggregate (Preview in 2.21.0 and later)
* If you use `skip` and/or `take` with `groupBy()`, you must also include `orderBy` in the query

Order by aggregate group [#order-by-aggregate-group]

You can **order by aggregate group**. Prisma ORM added support for using `orderBy` with aggregated groups in relational databases in version [2.21.0](https://github.com/prisma/prisma/releases/2.21.0) and support for MongoDB in [3.4.0](https://github.com/prisma/prisma/releases/3.4.0).

The following example sorts each `city` group by the number of users in that group (largest group first):

```ts
const groupBy = await prisma.user.groupBy({
  by: ["city"],
  _count: {
    city: true,
  },
  orderBy: {
    _count: {
      city: "desc",
    },
  },
});
```

```js no-copy
[
  { city: "Berlin", count: { city: 3 } },
  { city: "Paris", count: { city: 2 } },
  { city: "Amsterdam", count: { city: 1 } },
];
```

Order by field [#order-by-field]

The following query orders groups by country, skips the first two groups, and returns the 3rd and 4th group:

```ts
const groupBy = await prisma.user.groupBy({
  by: ["country"],
  _sum: {
    profileViews: true,
  },
  orderBy: {
    country: "desc",
  },
  skip: 2,
  take: 2,
});
```

groupBy() FAQ [#groupby-faq]

Can I use select with groupBy()? [#can-i-use-select-with-groupby]

You cannot use `select` with `groupBy()`. However, all fields included in `by` are automatically returned.

What is the difference between using where and having with groupBy()? [#what-is-the-difference-between-using-where-and-having-with-groupby]

`where` filters all records before grouping, and `having` filters entire groups and supports filtering on an aggregate field value, such as the average or sum of a particular field in that group.

What is the difference between groupBy() and distinct? [#what-is-the-difference-between-groupby-and-distinct]

Both `distinct` and `groupBy()` group records by one or more unique field values. `groupBy()` allows you to aggregate data within each group - for example, return the average number of views on posts from Denmark - whereas distinct does not.

Count [#count]

Count records [#count-records]

Use [`count()`](/orm/v6/reference/prisma-client-reference#count) to count the number of records or non-`null` field values. The following example query counts all users:

```ts
const userCount = await prisma.user.count();
```

Count relations [#count-relations]

> [!NOTE]
> This feature is generally available in version [3.0.1](https://github.com/prisma/prisma/releases/3.0.1) and later. To use this feature in versions before 3.0.1 the [Preview feature](/orm/v6/reference/preview-features/client-preview-features#enabling-a-prisma-client-preview-feature) `selectRelationCount` will need to be enabled.

To return a count of relations (for example, a user's post count), use the `_count` parameter with a nested `select` as shown:

```ts
const usersWithCount = await prisma.user.findMany({
  include: {
    _count: {
      select: { posts: true },
    },
  },
});
```

```js no-copy
{ id: 1, _count: { posts: 3 } },
{ id: 2, _count: { posts: 2 } },
{ id: 3, _count: { posts: 2 } },
{ id: 4, _count: { posts: 0 } },
{ id: 5, _count: { posts: 0 } }
```

The `_count` parameter:

* Can be used inside a top-level `include` *or* `select`
* Can be used with any query that returns records (including `delete`, `update`, and `findFirst`)
* Can return [multiple relation counts](#return-multiple-relation-counts)
* Can [filter relation counts](#filter-the-relation-count) (from version 4.3.0)

Return a relations count with include [#return-a-relations-count-with-include]

The following query includes each user's post count in the results:

```ts
const usersWithCount = await prisma.user.findMany({
  include: {
    _count: {
      select: { posts: true },
    },
  },
});
```

```js no-copy
{ id: 1, _count: { posts: 3 } },
{ id: 2, _count: { posts: 2 } },
{ id: 3, _count: { posts: 2 } },
{ id: 4, _count: { posts: 0 } },
{ id: 5, _count: { posts: 0 } }
```

Return a relations count with select [#return-a-relations-count-with-select]

The following query uses `select` to return each user's post count *and no other fields*:

```ts
const usersWithCount = await prisma.user.findMany({
  select: {
    _count: {
      select: { posts: true },
    },
  },
});
```

```js no-copy
{
  _count: {
    posts: 3;
  }
}
```

Return multiple relation counts [#return-multiple-relation-counts]

The following query returns a count of each user's `posts` and `recipes` and no other fields:

```ts
const usersWithCount = await prisma.user.findMany({
  select: {
    _count: {
      select: {
        posts: true,
        recipes: true,
      },
    },
  },
});
```

```js no-copy
{
  _count: {
    posts: 3,
    recipes: 9
  }
}
```

Filter the relation count [#filter-the-relation-count]

> [!NOTE]
> This feature is generally available in version `4.16.0` and later. To use this feature in versions [`4.3.0`](https://github.com/prisma/prisma/releases/tag/4.3.0) to [`4.15.0`](https://github.com/prisma/prisma/releases/tag/4.15.0) the [Preview feature](/orm/v6/reference/preview-features/client-preview-features#enabling-a-prisma-client-preview-feature) `filteredRelationCount` will need to be enabled.

Use `where` to filter the fields returned by the `_count` output type. You can do this on [scalar fields](/orm/v6/prisma-schema/data-model/models#scalar-fields), [relation fields](/orm/v6/prisma-schema/data-model/models#relation-fields) and fields of a [composite type](/orm/v6/prisma-schema/data-model/models#defining-composite-types).

For example, the following query returns all user posts with the title "Hello!":

```ts
// Count all user posts with the title "Hello!"
await prisma.user.findMany({
  select: {
    _count: {
      select: {
        posts: { where: { title: "Hello!" } },
      },
    },
  },
});
```

The following query finds all user posts with comments from an author named "Alice":

```ts
// Count all user posts that have comments
// whose author is named "Alice"
await prisma.user.findMany({
  select: {
    _count: {
      select: {
        posts: {
          where: { comments: { some: { author: { is: { name: "Alice" } } } } },
        },
      },
    },
  },
});
```

Count non-null field values [#count-non-null-field-values]

In [2.15.0](https://github.com/prisma/prisma/releases/2.15.0) and later, you can count all records as well as all instances of non-`null` field values. The following query returns a count of:

* All `User` records (`_all`)
* All non-`null` `name` values (not distinct values, just values that are not `null`)

```ts
const userCount = await prisma.user.count({
  select: {
    _all: true, // Count all records
    name: true, // Count all non-null field values
  },
});
```

```js no-copy
{ _all: 30, name: 10 }
```

Filtered count [#filtered-count]

`count` supports filtering. The following example query counts all users with more than 100 profile views:

```ts
const userCount = await prisma.user.count({
  where: {
    profileViews: {
      gte: 100,
    },
  },
});
```

The following example query counts a particular user's posts:

```ts
const postCount = await prisma.post.count({
  where: {
    authorId: 29,
  },
});
```

Select distinct [#select-distinct]

Prisma Client allows you to filter duplicate rows from a Prisma Query response to a [`findMany`](/orm/v6/reference/prisma-client-reference#findmany) query using [`distinct`](/orm/v6/reference/prisma-client-reference#distinct) . `distinct` is often used in combination with [`select`](/orm/v6/reference/prisma-client-reference#select) to identify certain unique combinations of values in the rows of your table.

The following example returns all fields for all `User` records with distinct `name` field values:

```ts
const result = await prisma.user.findMany({
  where: {},
  distinct: ["name"],
});
```

The following example returns distinct `role` field values (for example, `ADMIN` and `USER`):

```ts
const distinctRoles = await prisma.user.findMany({
  distinct: ["role"],
  select: {
    role: true,
  },
});
```

```js no-copy
[
  {
    role: "USER",
  },
  {
    role: "ADMIN",
  },
];
```

distinct under the hood [#distinct-under-the-hood]

Prisma Client's `distinct` option does not use SQL `SELECT DISTINCT`. Instead, `distinct` uses:

* A `SELECT` query
* In-memory post-processing to select distinct

It was designed in this way in order to **support `select` and `include`** as part of `distinct` queries.

The following example selects distinct on `gameId` and `playerId`, ordered by `score`, in order to return **each player's highest score per game**. The query uses `include` and `select` to include additional data:

* Select `score` (field on `Play`)
* Select related player name (relation between `Play` and `User`)
* Select related game name (relation between `Play` and `Game`)

<details>
  <summary>
    Expand for sample schema
  </summary>

  ```prisma
  model User {
    id   Int     @id @default(autoincrement())
    name String?
    play Play[]
  }

  model Game {
    id   Int     @id @default(autoincrement())
    name String?
    play Play[]
  }

  model Play {
    id       Int   @id @default(autoincrement())
    score    Int?  @default(0)
    playerId Int?
    player   User? @relation(fields: [playerId], references: [id])
    gameId   Int?
    game     Game? @relation(fields: [gameId], references: [id])
  }
  ```
</details>

```ts
const distinctScores = await prisma.play.findMany({
  distinct: ["playerId", "gameId"],
  orderBy: {
    score: "desc",
  },
  select: {
    score: true,
    game: {
      select: {
        name: true,
      },
    },
    player: {
      select: {
        name: true,
      },
    },
  },
});
```

```text no-copy
[
  {
    score: 900,
    game: { name: 'Pacman' },
    player: { name: 'Bert Bobberton' }
  },
  {
    score: 400,
    game: { name: 'Pacman' },
    player: { name: 'Nellie Bobberton' }
  }
]
```

Without `select` and `distinct`, the query would return:

```
[
  {
    gameId: 2,
    playerId: 5
  },
  {
    gameId: 2,
    playerId: 10
  }
]
```

## Related pages

- [`Case sensitivity`](https://www.prisma.io/docs/orm/v6/prisma-client/queries/case-sensitivity): How Prisma Client handles case sensitivity when filtering and sorting.
- [`Computed fields`](https://www.prisma.io/docs/orm/v6/prisma-client/queries/computed-fields): This page explains how to use client extensions to add computed fields to Prisma models.
- [`CRUD`](https://www.prisma.io/docs/orm/v6/prisma-client/queries/crud): How to perform CRUD with Prisma Client.
- [`Custom models`](https://www.prisma.io/docs/orm/v6/prisma-client/queries/custom-models): This page explains how to wrap Prisma Client in custom models
- [`Custom validation`](https://www.prisma.io/docs/orm/v6/prisma-client/queries/custom-validation): This page explains how to add custom validation to Prisma Client