Per-group reporting
Summarize activity by geography, category, or another model field.
Prisma · Function
groupBy
Prisma Client groupBy() groups records by one or more fields and calculates aggregates for each group. It supports pre-group filtering with where, post-group filtering with having, and ordering grouped results.
- Since
- 2.20.0
How it works
Call groupBy() on a Prisma Client model with one or more fields in by. Prisma filters matching records with where, forms groups, calculates requested aggregates such as _count or _avg, and can filter completed groups with having or sort them with orderBy.
Syntax
prisma.<model>.groupBy({ by, where?, having?, orderBy?, take?, skip?, _count?, _avg?, _sum?, _min?, _max? })Parameters
| Name | Type | Description |
|---|---|---|
by | string | string[] | Fields whose values define the groups; provide one or more model field names. |
where | ModelWhereInput | Filters records before grouping and aggregation. |
_count, _avg, _min, _max, _sum | aggregate selections | Requests aggregate values for each group, including count, average, minimum, maximum, or sum. |
having | ModelScalarWhereWithAggregatesInput | Filters complete groups using field or aggregate conditions. |
orderBy | OrderByWithAggregationInput | OrderByWithAggregationInput[] | Controls the ordering of grouped rows, including supported aggregate ordering. |
take | number | Limits the number of grouped rows returned. |
skip | number | Skips grouped rows before returning results. |
Returns
An array containing one result object per group, with the grouping fields and requested aggregate values.
Key points
- Use
byto create groups from one field or a combination of fields. - Request aggregate calculations independently for every resulting group.
- For one grouping field,
by: 'fieldName'is a supported shorthand. - Prisma provides separate filters for input records and completed groups.
- Grouping fields appear in each result without a separate selection clause.
- Grouped aggregation is useful for per-category statistics such as averages.
Common mistakes
selectis not supported for this operation; fields listed inbyare returned automatically.- Putting row-level filters in
whereprevents excluded records from contributing to aggregates. - Use
havingfor aggregate-based group filtering, notwhere.
Example: Group users and calculate aggregates
const groupedUsers = await prisma.user.groupBy({
by: ['country'],
where: { email: { endsWith: 'prisma.io' } },
_count: { _all: true },
_avg: { profileViews: true },
orderBy: { _avg: { profileViews: 'desc' } },
});
console.log(groupedUsers);Groups matching users by country, then returns each country's row count and average profile views.
When to use it
Grouped statistics
Find averages, totals, extrema, or row counts for each group.
Aggregate-based filtering
Return only groups meeting an aggregate threshold.
Interview questions
QWhat does Prisma Client groupBy() do?
groupBy() groups records by the fields in by and computes requested aggregates for each group. For example, _sum, _avg, _min, _max, and _count can be used to summarize grouped data.
Follow-up: How would you avoid aggregating unwanted rows? Use where, because it filters records before grouping; use having only when filtering completed groups by aggregate values.
QWhat is the difference between where and having in groupBy()?
where filters individual records before Prisma creates groups. having filters whole groups afterward and can compare aggregate values such as an average or sum.
Follow-up: Why can having be more expensive than where? It operates after grouping, so the database may need to form groups from more input rows first.
QHow are grouped fields selected and returned?
The fields named in by are returned automatically, and Prisma does not allow select with groupBy(). A single grouping field can use the shorthand string form instead of an array.
Follow-up: How can you order grouped results? Add orderBy, including aggregate ordering such as ordering by a count or average.
Related
aggregate()count()findMany()distincthavingorderBy