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

Aggregate values are nullable

In 2.21.0 and later, aggregations on nullable fields can return a number or null. This excludes count, which always returns 0 if no records are found.

Consider the following query, where age is nullable in the schema:

const aggregations = await prisma.user.aggregate({
  _avg: {
    age: true,
  },
  _count: {
    age: true,
  },
});

{
  _avg: {
    age: null
  },
  _count: {
    age: 9
  }
}

The query returns { _avg: { age: null } } in either of the following scenarios:

• There are no users
• The value of every user's age field is null

This allows you to differentiate between the true aggregate value (which could be zero) and no data.

Group by

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. groupBy() is a GA in 2.20.0 and later.

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

[
  { country: "Germany", _sum: { profileViews: 126 } },
  { country: "Sweden", _sum: { profileViews: 0 } },
];

If you have a single element in the by option, you can use the following shorthand syntax to express your query:

const groupUsers = await prisma.user.groupBy({
  by: "country",
});

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: {
    // [!code highlight]
    email: {
      // [!code highlight]
      contains: "prisma.io", // [!code highlight]
    }, // [!code highlight]
  }, // [!code highlight]
  _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: {
    // [!code highlight]
    profileViews: {
      // [!code highlight]
      _avg: {
        // [!code highlight]
        gt: 100, // [!code highlight]
      }, // [!code highlight]
    }, // [!code highlight]
  }, // [!code highlight]
});

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: {
      // [!code highlight]
      notIn: ["Sweden", "Ghana"], // [!code highlight]
    }, // [!code highlight]
  },
  _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: {
      // [!code highlight]
      not: "Sweden", // [!code highlight]
    }, // [!code highlight]
  },
  _sum: {
    profileViews: true,
  },
  having: {
    country: {
      // [!code highlight]
      not: "Ghana", // [!code highlight]
    }, // [!code highlight]
    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 orderBy fields that are present in by
• You can orderBy aggregate (Preview in 2.21.0 and later)
• If you use skip and/or take with groupBy(), you must also include orderBy in the query

Order by aggregate group

You can order by aggregate group. Prisma ORM added support for using orderBy with aggregated groups in relational databases in version 2.21.0 and support for MongoDB in 3.4.0.

The following example sorts each city group by the number of users in that group (largest group first):

const groupBy = await prisma.user.groupBy({
  by: ["city"],
  _count: {
    city: true,
  },
  orderBy: {
    _count: {
      city: "desc",
    },
  },
});

[
  { city: "Berlin", count: { city: 3 } },
  { city: "Paris", count: { city: 2 } },
  { city: "Amsterdam", count: { city: 1 } },
];

Order by field

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

Count records

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 relations

:::info

This feature is generally available in version 3.0.1 and later. To use this feature in versions before 3.0.1 the Preview feature selectRelationCount will need to be enabled.

:::

To return a count of relations (for example, a user's post count), use the _count parameter with a nested select as shown:

const usersWithCount = await prisma.user.findMany({
  include: {
    _count: {
      select: { posts: true },
    },
  },
});

{ id: 1, _count: { posts: 3 } },
{ id: 2, _count: { posts: 2 } },
{ id: 3, _count: { posts: 2 } },
{ id: 4, _count: { posts: 0 } },
{ id: 5, _count: { posts: 0 } }

The _count parameter:

• Can be used inside a top-level include or select
• Can be used with any query that returns records (including delete, update, and findFirst)
• Can return multiple relation counts
• Can filter relation counts (from version 4.3.0)

Return a relations count with include

The following query includes each user's post count in the results:

const usersWithCount = await prisma.user.findMany({
  include: {
    _count: {
      select: { posts: true },
    },
  },
});

{ id: 1, _count: { posts: 3 } },
{ id: 2, _count: { posts: 2 } },
{ id: 3, _count: { posts: 2 } },
{ id: 4, _count: { posts: 0 } },
{ id: 5, _count: { posts: 0 } }

Return a relations count with select

The following query uses select to return each user's post count and no other fields:

const usersWithCount = await prisma.user.findMany({
  select: {
    _count: {
      select: { posts: true },
    },
  },
});

{
  _count: {
    posts: 3;
  }
}

Return multiple relation counts

The following query returns a count of each user's posts and recipes and no other fields:

const usersWithCount = await prisma.user.findMany({
  select: {
    _count: {
      select: {
        posts: true,
        recipes: true,
      },
    },
  },
});

{
  _count: {
    posts: 3,
    recipes: 9
  }
}

Filter the relation count

:::info

This feature is generally available in version 4.16.0 and later. To use this feature in versions 4.3.0 to 4.15.0 the Preview feature filteredRelationCount will need to be enabled.

:::

Use where to filter the fields returned by the _count output type. You can do this on scalar fields, relation fields and fields of a composite type.

For example, the following query returns all user posts with the title "Hello!":

// Count all user posts with the title "Hello!"
await prisma.user.findMany({
  select: {
    _count: {
      select: {
        posts: { where: { title: "Hello!" } },
      },
    },
  },
});

The following query finds all user posts with comments from an author named "Alice":

// Count all user posts that have comments
// whose author is named "Alice"
await prisma.user.findMany({
  select: {
    _count: {
      select: {
        posts: {
          where: { comments: { some: { author: { is: { name: "Alice" } } } } },
        },
      },
    },
  },
});

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

{ _all: 30, name: 10 }

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

[
  {
    role: "USER",
  },
  {
    role: "ADMIN",
  },
];

distinct under the hood

Prisma Client's distinct option does not use SQL SELECT DISTINCT. Instead, 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?
  play Play[]
}

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

[
  {
    score: 900,
    game: { name: 'Pacman' },
    player: { name: 'Bert Bobberton' }
  },
  {
    score: 400,
    game: { name: 'Pacman' },
    player: { name: 'Nellie Bobberton' }
  }
]

Without select and distinct, the query would return:

[
  {
    gameId: 2,
    playerId: 5
  },
  {
    gameId: 2,
    playerId: 10
  }
]