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

> For the complete Prisma documentation index, see [llms.txt](https://www.prisma.io/docs/llms.txt). A markdown version of any docs page is available by appending `.md` to its URL.

Use Prisma Client to aggregate, group by, count, and select distinct.

Location: ORM > v7 > 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`](https://www.prisma.io/docs/orm/v7/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]

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

```json
{
  "_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()`](https://www.prisma.io/docs/orm/v7/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.

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

```json
[
  { 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
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
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
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
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**. 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',
    },
  },
});
```

```json
[
  { 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()`](https://www.prisma.io/docs/orm/v7/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]

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

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

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

```json
{
  _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,
      },
    },
  },
});
```

```json
{
  "_count": {
    "posts": 3,
    "recipes": 9
  }
}
```

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

Use `where` to filter the fields returned by the `_count` output type. You can do this on [scalar fields](https://www.prisma.io/docs/orm/v7/prisma-schema/data-model/models#scalar-fields) and [relation fields](https://www.prisma.io/docs/orm/v7/prisma-schema/data-model/models#relation-fields).

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

```json
{ "_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`](https://www.prisma.io/docs/orm/v7/reference/prisma-client-reference#findmany) query using [`distinct`](https://www.prisma.io/docs/orm/v7/reference/prisma-client-reference#distinct) . `distinct` is often used in combination with [`select`](https://www.prisma.io/docs/orm/v7/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,
  },
});
```

```json
[
  { 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`)

**Expand for sample schema**

```prisma title="schema.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])
}
```

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

```json
[
  {
    "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:

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

## Related pages

- [`CRUD`](https://www.prisma.io/docs/orm/v7/prisma-client/queries/crud): Learn how to perform create, read, update, and delete operations
- [`Excluding fields`](https://www.prisma.io/docs/orm/v7/prisma-client/queries/excluding-fields): Learn how to exclude fields from Prisma Client results with the omit option.
- [`Filtering and sorting`](https://www.prisma.io/docs/orm/v7/prisma-client/queries/filtering-and-sorting): Learn how to filter Prisma Client queries with where and sort results with orderBy.
- [`Full-text search`](https://www.prisma.io/docs/orm/v7/prisma-client/queries/full-text-search): Learn how to search text fields with Prisma Client using your database's native full-text search support.
- [`Pagination`](https://www.prisma.io/docs/orm/v7/prisma-client/queries/pagination): Learn how to paginate Prisma Client query results with offset pagination and cursor-based pagination.