# Aggregation, grouping, and summarizing (Prisma ORM v6) (/docs/orm/v6/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 > 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`](https://www.prisma.io/docs/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/orm/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()`](https://www.prisma.io/docs/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/orm/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/orm/releases/2.21.0) and support for MongoDB in [3.4.0](https://github.com/prisma/orm/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()`](https://www.prisma.io/docs/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/orm/releases/3.0.1) and later. To use this feature in versions before 3.0.1 the [Preview feature](https://www.prisma.io/docs/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/orm/releases/tag/4.3.0) to [`4.15.0`](https://github.com/prisma/orm/releases/tag/4.15.0) the [Preview feature](https://www.prisma.io/docs/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](https://www.prisma.io/docs/orm/v6/prisma-schema/data-model/models#scalar-fields), [relation fields](https://www.prisma.io/docs/orm/v6/prisma-schema/data-model/models#relation-fields) and fields of a [composite type](https://www.prisma.io/docs/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/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 }, }); ``` ```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`](https://www.prisma.io/docs/orm/v6/reference/prisma-client-reference#findmany) query using [`distinct`](https://www.prisma.io/docs/orm/v6/reference/prisma-client-reference#distinct) . `distinct` is often used in combination with [`select`](https://www.prisma.io/docs/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`) **Expand for sample 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, }, }, }, }); ``` ```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