Group by (Reference)

Prisma Client allows you to group records by one or more field values - such as country, or country and city - and perform aggregations on each group. The following example groups all users by the country field and returns the total number of profile views for each country:

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

Hide CLI output

[
  { country: 'Germany', sum: { profileViews: 126 } },
  { country: 'Sweden', sum: { profileViews: 0 } },
]

groupBy is a preview feature. To enable this feature, add "groupBy" to the generator block in your schema as shown and run prisma generate:

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["groupBy"]
}

groupBy and filtering

groupBy supports two levels of filtering: where and having.

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:

const groupUsers = await prisma.user.groupBy({
  by: ["country"],
  where: {
    email: {
      contains: "prisma.io"
    }
  },
  sum: {
    profileViews: true
  }
})

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:

const groupUsers = await prisma.user.groupBy({
  by: ["country"],
  where: {
    email: {
      contains: "prisma.io"
    }
  },
  sum: {
    profileViews: true
  },
  having: {
    profileViews: {
      avg: {
        gt: 100
      }
    }
  }
})

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:

const fd = await prisma.user.groupBy({
  by: ["country"],
  where: {
      country: {
        notIn: ["Sweden", "Ghana"]
      }
  },
  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.

const groupUsers = await prisma.user.groupBy({
  by: ["country"],
  where: {
    country: {
      not: "Sweden"
    }
  },
  sum: {
    profileViews: true
  },
  having: {
    country: {
      not: "Ghana"
    }
     profileViews: {
       min: {
        gte: 10
      }
    }
  }
})

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

groupBy and ordering

The following constraints apply when you combine groupBy and orderBy:

You can only orderBy fields that are present in by
If you use skip and/or take with groupBy, you must also include orderBy in the query

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

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

Generated types

The following types relating to group by are generated from the User model:

export type GroupByUserArgs = {
  where?: UserWhereInput
  orderBy?: Enumerable<UserOrderByInput>
  by: Array<UserScalarFieldEnum>
  having?: UserScalarWhereWithAggregatesInput
  take?: number
  skip?: number
  count?: UserCountAggregateInputType
  avg?: UserAvgAggregateInputType
  sum?: UserSumAggregateInputType
  min?: UserMinAggregateInputType
  max?: UserMaxAggregateInputType
}

FAQ

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`?

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`?

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.

Was this helpful?

Edit this page on GitHub

Group by

groupBy and filtering

Filter records with `where`

Filter groups with having

Use case for having

groupBy and ordering

Generated types

FAQ

Can I use `select` with `groupBy`?

What is the difference between using `where` and `having` with `groupBy`?

What is the difference between `groupBy` and `distinct`?

Was this helpful?

Products

Resources

Prisma With

Community

Company

Newsletter

Find Us

Group by

groupBy and filtering

Filter records with where

Filter groups with having

Use case for having

groupBy and ordering

Generated types

FAQ

Can I use select with groupBy?

What is the difference between using where and having with groupBy?

What is the difference between groupBy and distinct?

Was this helpful?

Products

Resources

Prisma With

Community

Company

Newsletter

Find Us

Filter records with `where`

Can I use `select` with `groupBy`?

What is the difference between using `where` and `having` with `groupBy`?

What is the difference between `groupBy` and `distinct`?