Aggregation & Grouping

Prisma Intermediate/Advanced

Chapter 2 · Aggregation & Grouping

So far every query has returned records. Real applications also need numbers about records: how many posts are published, the average view count, which author wrote the most. Fetching every row and adding it up in JavaScript works for ten posts and falls over at ten thousand. This chapter shows how to make the database do that arithmetic, using the same blog schema as before.

Counting: count()

const total     = await prisma.post.count();
const published = await prisma.post.count({ where: { published: true } });

// Count all rows AND the rows where a nullable field is filled in
const users = await prisma.user.count({
  select: { _all: true, name: true },
});
// { _all: 42, name: 37 }  -> 5 users have no name

count() accepts the same where filters as findMany, so everything from Prisma Fundamentals, Chapter 6 carries over.

Summary Statistics: aggregate()

const stats = await prisma.post.aggregate({
  where:  { published: true },
  _count: { _all: true },
  _sum:   { viewCount: true },
  _avg:   { viewCount: true },
  _max:   { viewCount: true, createdAt: true },
  _min:   { createdAt: true },
});

// {
//   _count: { _all: 18 },
//   _sum:   { viewCount: 2140 },
//   _avg:   { viewCount: 118.9 },
//   _max:   { viewCount: 610, createdAt: 2026-09-20T... },
//   _min:   { createdAt: 2026-03-02T... }
// }

Operator	Works on	Notes
_count	Any field, or `_all`	Counts non-null values; always a number, `0` when nothing matches
_sum, _avg	Numeric fields	`_avg` of an `Int` field can be a fraction
_min, _max	Numbers, dates, strings	Handy for "oldest" and "newest"

No rows means null, not zero

If the where matches nothing, _sum, _avg, _min and _max come back as null. Only _count is guaranteed to be a number. Handle it explicitly — stats._sum.viewCount ?? 0 — rather than letting null reach a template or a calculation.

Per-Group Figures: groupBy()

aggregate gives one set of numbers for everything matched. groupBy gives one set per group — the Prisma equivalent of SQL's GROUP BY.

// Published post count and total views, per author
const perAuthor = await prisma.post.groupBy({
  by:     ["authorId"],
  where:  { published: true },
  _count: { _all: true },
  _sum:   { viewCount: true },
  orderBy: { _sum: { viewCount: "desc" } },
});
// [
//   { authorId: 3, _count: { _all: 7 }, _sum: { viewCount: 1200 } },
//   { authorId: 1, _count: { _all: 9 }, _sum: { viewCount: 820 } },
//   ...
// ]

where vs. having

Option	Filters	Example
where	Individual rows, before grouping	Only published posts
having	Whole groups, after grouping	Only authors with at least 5 posts

// Authors with 5 or more published posts
const prolific = await prisma.post.groupBy({
  by:     ["authorId"],
  where:  { published: true },
  _count: { _all: true },
  having: { authorId: { _count: { gte: 5 } } },
});

Inside having you can only filter on aggregate values, or on fields listed in by.

Paging groups needs orderBy

If you use skip or take with groupBy, you must also give an orderBy. Without a defined order, "the first ten groups" doesn't mean anything, so Prisma refuses the query.

groupBy returns IDs, not names

Groups contain only the by fields and the aggregates — groupBy can't include relations. To show author names, fetch them in a second query and join in code:

const authors = await prisma.user.findMany({
  where:  { id: { in: perAuthor.map((g) => g.authorId) } },
  select: { id: true, name: true },
});
const nameById = new Map(authors.map((a) => [a.id, a.name]));

const report = perAuthor.map((g) => ({
  author: nameById.get(g.authorId) ?? "(unknown)",
  posts:  g._count._all,
  views:  g._sum.viewCount ?? 0,
}));

Two queries, whatever the number of authors — not one query per author.

Counting Relations: _count

Often the simplest option is to ask for a count alongside each record, using _count inside select or include:

const authors = await prisma.user.findMany({
  select: {
    name: true,
    _count: {
      select: {
        posts: { where: { published: true } },  // count only published posts
      },
    },
  },
  orderBy: { posts: { _count: "desc" } },          // most posts first
});
// [{ name: "Ada", _count: { posts: 9 } }, ...]

Which one should I use?

Use relation _count when you're listing records anyway and want a number next to each. Use groupBy when the numbers are the result. Note that orderBy on a relation count sorts by all related posts; the where inside _count only affects the number returned.

distinct

// Which roles are actually in use?
const roles = await prisma.user.findMany({
  distinct: ["role"],
  select:   { role: true },
});

Prisma applies distinct by filtering results after they're fetched rather than with SQL's SELECT DISTINCT. It's convenient, but on a large table it still reads every matching row — for "how many of each," prefer groupBy.

A Stats Endpoint for the Blog API

app.get("/stats", async (req, res) => {
  const [posts, drafts, views, topTags] = await prisma.$transaction([
    prisma.post.count({ where: { published: true } }),
    prisma.post.count({ where: { published: false } }),
    prisma.post.aggregate({ where: { published: true }, _sum: { viewCount: true } }),
    prisma.tag.findMany({
      select:  { name: true, _count: { select: { posts: true } } },
      orderBy: { posts: { _count: "desc" } },
      take:    5,
    }),
  ]);

  res.json({
    published:  posts,
    drafts,
    totalViews: views._sum.viewCount ?? 0,
    topTags:    topTags.map((t) => ({ name: t.name, posts: t._count.posts })),
  });
});

Wrapping the four reads in $transaction([...]) from Chapter 1 means they all see the same snapshot of the data, so the numbers are consistent with each other.

Hands-On Exercises

Exercise 1

Write one query that returns the number of published posts, the total and average view count, and the date of the most recent published post. Then run it with a where that matches nothing and show safe handling of the null values.

📄 View solution

Exercise 2

Using groupBy, list authors with at least 3 published posts and an average view count above 50, most-viewed first, showing each author's name.

📄 View solution

Exercise 3

Add GET /tags to the blog API, returning every tag with its number of published posts, and add ?unused=true support that returns only tags with no posts at all. Explain which technique you used for each and why.

📄 View solution

Chapter 2 Quick Reference

count({ where }); select: { _all: true, field: true } counts rows and non-null values
aggregate with _count, _sum, _avg, _min, _max
Empty results: _count is 0, everything else is null — use ?? 0
groupBy({ by, where, having, orderBy }); where filters rows, having filters groups
skip/take on groupBy require orderBy; groups hold IDs, so look names up separately
Relation counts: _count: { select: { posts: { where } } }; sort with orderBy: { posts: { _count: "desc" } }
distinct filters in memory; prefer groupBy for counts on big tables