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

1const aggregations = await prisma.user.aggregate({
2 avg: {
3 age: true,
4 },
5});
6
7console.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:

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

Prerequisites

This is a preview feature. Add the highlighted feature flag to enable aggregation capabilities:

1generator client {
2 provider = "prisma-client-js"
3 previewFeatures = ["aggregateApi"]
4}

Please help us make this feature production-ready by trying it out, sharing your feedback and reporting bugs! The more and better feedback we receive, the earlier the feature can be released for production usage.

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
1const aggregations = await prisma.user.aggregate({
2 avg: {
3 age: true,
4 },
5 where: {
6 email: {
7 contains: "prisma.io",
8 },
9 },
10 orderBy: {
11 age: "asc",
12 },
13 take: 10,
14});
15
16console.log("Average age:" + aggregations.avg.age);

Examples

The following examples are based on a sample schema:

1generator client {
2 provider = "prisma-client-js"
3}
4
5datasource db {
6 provider = "mysql"
7 url = env("DATABASE_URL")
8}
9
10model Post {
11 id Int @default(autoincrement()) @id
12 authorId Int?
13 content String?
14 published Boolean @default(false)
15 title String
16 adEarnings Float?
17 user User? @relation(fields: [authorId], references: [id])
18
19 @@index([authorId], name: "authorId")
20}
21
22model User {
23 id Int @default(autoincrement()) @id
24 email String @unique
25 name String?
26 extendedProfile Json?
27 age Int?
28 role Role @default(USER)
29 posts Post[]
30}
31
32enum Role {
33 ADMIN
34 USER
35 MODERATOR
36}

Return min and max age of all User records

1const minMaxAge = await prisma.user.aggregate({
2 max: {
3 age: true,
4 },
5 min: {
6 age: true,
7 },
8});

Example output:

1{
2 "max": {
3 "age": 24
4 },
5 "min": {
6 "age": 93
7 }
8}

Return sum of all Post record adEarnings fields

1const setValue = await prisma.post.aggregate({
2 sum: {
3 adEarnings: true,
4 },
5});

Example output:

1{
2 "sum": {
3 "adEarnings": 18.376294613270932
4 }
5}

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:

1export type AggregateUser = {
2 count: number
3 avg: UserAvgAggregateOutputType | null
4 sum: UserSumAggregateOutputType | null
5 min: UserMinAggregateOutputType | null
6 max: UserMaxAggregateOutputType | null
7}
8
9export type UserAvgAggregateOutputType = {
10 id: number
11 age: number
12}
13
14export type UserSumAggregateOutputType = {
15 id: number
16 age: number | null
17}
18
19export type UserMinAggregateOutputType = {
20 id: number
21 age: number | null
22}
23
24export type UserMaxAggregateOutputType = {
25 id: number
26 age: number | null
27}
28
29
30export type UserAvgAggregateInputType = {
31 id?: true
32 age?: true
33}
34
35export type UserSumAggregateInputType = {
36 id?: true
37 age?: true
38}
39
40export type UserMinAggregateInputType = {
41 id?: true
42 age?: true
43}
44
45export type UserMaxAggregateInputType = {
46 id?: true
47 age?: true
48}
49
50export type AggregateUserArgs = {
51 where?: UserWhereInput
52 orderBy?: UserOrderByInput
53 cursor?: UserWhereUniqueInput
54 take?: number
55 skip?: number
56 distinct?: Enumerable<UserDistinctFieldEnum>
57 count?: true
58 avg?: UserAvgAggregateInputType
59 sum?: UserSumAggregateInputType
60 min?: UserMinAggregateInputType
61 max?: UserMaxAggregateInputType
62}
Edit this page on Github