Aggregations

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
}

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

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

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

{
  "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
}