Skip to content
Tutorials Center
My learning

Topics

Lesson 14 of 36 ยท Client Queries

Counting, Aggregating and Grouping with Prisma

Summarize data with Prisma Client: count, aggregate (_sum, _avg, _min, _max), groupBy with having and ordering, and when to use these instead of loading rows into JavaScript.

Dashboards, reports and "12 items in stock" labels all need summaries, not rows. The wrong way is to load every row and add them up in JavaScript. The right way is to let the database do it: it is faster, uses less memory and sends only the answer over the network. Prisma exposes three tools: count, aggregate and groupBy.

count

TypeScript
const all = await prisma.product.count();
const kitchen = await prisma.product.count({ where: { category: "kitchen" } });

const byField = await prisma.product.count({
  select: { _all: true, stock: true },       // _all counts rows; stock counts non-null values
});
// { _all: 42, stock: 42 }

count with a where is how you build the total for pagination. count({ select: { fieldName: true } }) counts only the rows where that field is not null, mirroring SQL's COUNT(column).

aggregate

aggregate computes functions over the rows that match a filter:

TypeScript
const stats = await prisma.product.aggregate({
  where: { category: "office" },
  _count: { _all: true },
  _sum: { stock: true },
  _avg: { priceCents: true },
  _min: { priceCents: true },
  _max: { priceCents: true },
});
// { _count: { _all: 3 }, _sum: { stock: 420 }, _avg: { priceCents: 1366.67 }, _min: {...}, _max: {...} }
Key Works on SQL
_count any field or _all COUNT
_sum numeric fields SUM
_avg numeric fields AVG
_min, _max numbers, strings, dates MIN, MAX

Results of _sum, _avg, _min and _max are null when no rows match (an empty set has no average), so write stats._sum.stock ?? 0. Fields of type Decimal return Decimal objects, and BigInt fields return bigint.

aggregate also accepts orderBy, skip, take and cursor, which let you aggregate over "the first 100 rows" of a sorted set.

groupBy

groupBy is GROUP BY: one result row per distinct value of the grouping fields.

TypeScript
const perCategory = await prisma.product.groupBy({
  by: ["category"],
  _count: { _all: true },
  _sum: { stock: true },
  _avg: { priceCents: true },
  orderBy: { _sum: { stock: "desc" } },
});
// [{ category: 'office', _count: { _all: 3 }, _sum: { stock: 420 }, _avg: {...} }, ...]

Rules to remember:

  • by lists the fields you group on; they are returned in each result row.
  • Every selected field must be either in by or inside an aggregate (_count, _sum, ...).
  • where filters rows before grouping (SQL WHERE).
  • having filters groups after aggregation (SQL HAVING).
  • orderBy may use grouped fields or aggregates, and is required if you use skip or take.
TypeScript
const bigCategories = await prisma.product.groupBy({
  by: ["category"],
  where: { stock: { gt: 0 } },                       // rows with stock only
  _count: { _all: true },
  having: { priceCents: { _avg: { gt: 1000 } } },    // groups whose average price > 1000
  orderBy: { category: "asc" },
});

You can group by several fields (by: ["category", "sku"]) to get one row per combination.

Try it: the three tools on sample data

The stand-in below builds count, aggregate and groupBy on arrays, with the same result shapes. It uses a small where helper for equality filters; the aggregate maths is the point.

Node.js Stand-in: count, aggregate, groupBy

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

The same queries in SQL

It helps to know what the database actually runs. Here are the SQL equivalents on real SQLite, so you can see that groupBy is plain GROUP BY ... HAVING and that where and having run at different stages.

Node.js aggregate and groupBy as SQL

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

When to aggregate in the database

Use count, aggregate and groupBy for totals, averages, leaderboards and chart data. Do the grouping in JavaScript only for tiny result sets. For things Prisma does not model (window functions, COUNT(DISTINCT ...) with complex joins, percentile functions) use $queryRaw (chapter 6).

Counting related rows is a separate feature, _count inside select or include (chapter 4): include: { _count: { select: { posts: true } } }.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does aggregate return for _avg when no rows match the filter?

    An empty set has no average, so _sum, _avg, _min and _max are null. Use ?? 0 where needed.
  2. 2. What is the difference between where and having in groupBy?

    Like SQL WHERE and HAVING: first rows are filtered, then groups are built, then groups are filtered.
  3. 3. Which query gives the number of products per category?

    groupBy with _count returns one row per category with its count.
  4. 4. Why prefer aggregate over loading rows and summing in JavaScript?

    Aggregating in the database saves memory, bandwidth and time on large tables.

Summary

  • count returns a number (or counts per field with select); aggregate returns _count, _sum, _avg, _min, _max.
  • groupBy needs by; use where before grouping and having after, and orderBy for sorted groups.
  • Aggregates over an empty set are null; Decimal and BigInt fields keep their own types.
  • Let the database summarize; use raw SQL only for what Prisma cannot express.

Next: One-to-Many Relations