Prisma Client allows you to perform aggregations operations on the number fields (such as Int and Float) of a model - for example, you can get the average age of all users (based on the sample model):

const aggregations = await prisma.user.aggregate({
avg: {
age: true,
},
});
console.log("Average age:" + aggregations.avg.age);

Prisma Client supports the following aggregations:

  • avg (average)
  • sum (sum)
  • min (minimum value)
  • max (maximum value)

count is already available, and can be used in the following way:

const userCount = await prisma.user.count();

Aggregations, filtering, and ordering

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

Examples

The following examples are based on a sample schema:

generator client {
provider = "prisma-client-js"
}
datasource db {
provider = "mysql"
url = env("DATABASE_URL")
}
model Post {
id Int @default(autoincrement()) @id
authorId Int?
content String?
published Boolean @default(false)
title String
adEarnings Float?
user User? @relation(fields: [authorId], references: [id])
@@index([authorId], name: "authorId")
}
model User {
id Int @default(autoincrement()) @id
email String @unique
name String?
extendedProfile Json?
age Int?
role Role @default(USER)
posts Post[]
}
enum Role {
ADMIN
USER
MODERATOR
}

Return min and max age of all User records

const minMaxAge = await prisma.user.aggregate({
max: {
age: true,
},
min: {
age: true,
},
});

Example output:

{
"max": {
"age": 24
},
"min": {
"age": 93
}
}

Return sum of all Post record adEarnings fields

const setValue = await prisma.post.aggregate({
sum: {
adEarnings: true,
},
});

Example output:

{
"sum": {
"adEarnings": 18.376294613270932
}
}

Generated types

The following types relating to aggregation are generated from the User model in the sample schema - aggregation types are generated for any model with number fields:

export type AggregateUser = {
count: number
avg: UserAvgAggregateOutputType | null
sum: UserSumAggregateOutputType | null
min: UserMinAggregateOutputType | null
max: UserMaxAggregateOutputType | null
}
export type UserAvgAggregateOutputType = {
id: number
age: number
}
export type UserSumAggregateOutputType = {
id: number
age: number | null
}
export type UserMinAggregateOutputType = {
id: number
age: number | null
}
export type UserMaxAggregateOutputType = {
id: number
age: number | null
}
export type UserAvgAggregateInputType = {
id?: true
age?: true
}
export type UserSumAggregateInputType = {
id?: true
age?: true
}
export type UserMinAggregateInputType = {
id?: true
age?: true
}
export type UserMaxAggregateInputType = {
id?: true
age?: true
}
export type AggregateUserArgs = {
where?: UserWhereInput
orderBy?: Enumerable<UserOrderByInput>
cursor?: UserWhereUniqueInput
take?: number
skip?: number
distinct?: Enumerable<UserDistinctFieldEnum>
count?: true
avg?: UserAvgAggregateInputType
sum?: UserSumAggregateInputType
min?: UserMinAggregateInputType
max?: UserMaxAggregateInputType
}
Edit this page on GitHub