Aggregation, grouping, and summarizing

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

Aggregate

Prisma Client allows you to aggregate on the number fields (such as Int and Float) of a model. The following query returns the average age of all users:

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
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)

Group by (Preview)

Prisma Client's 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 video uses groupBy to summarize total COVID-19 cases by continent:

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,
},
})
Show CLI output

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

groupBy 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.

Count

Use count to count the number of records or non-null field values. The following example query counts all users:

const userCount = await prisma.user.count()

Count non-null field values

In 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)
const userCount = await prisma.user.count({
select: {
_all: true, // Count all records
name: true, // Count all non-null field values
},
})
Show CLI output

Filtered count

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

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

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

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

Select distinct

Prisma Client allows you to filter duplicate rows from a Prisma Query response to a findMany query using distinct . distinct is often used in combination with 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:

const result = await prisma.user.findMany({
where: {},
distinct: ['name'],
})

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

const distinctRoles = await prisma.user.findMany({
distinct: ['role'],
select: {
role: true,
},
})
Show CLI output

distinct under the hood

Prisma's distinct option does not use SQL SELECT DISTINCT. Instad, 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)
model User {
id Int @id @default(autoincrement())
name String?
Play Play[]
}
model Game {
id Int @id @default(autoincrement())
name String?
}
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)
}
const distinctScores = await prisma.play.findMany({
distinct: ['playerId', 'gameId'],
orderBy: {
score: 'desc',
},
select: {
score: true,
game: {
select: {
name: true,
},
},
player: {
select: {
name: true,
},
},
},
})
Show CLI output

Without select and distinct, the query would return:

[
{
gameId: 2,
playerId: 5
},
{
gameId: 2,
playerId: 10
}
]
Edit this page on GitHub