aggregate
Compute count, sum, average, min, and max across records
Run one or more aggregate functions over the records matching a filter:
const stats = await client.post.aggregate({
where: { published: true },
_count: true,
_avg: { views: true },
_sum: { views: true },
_min: { createdAt: true },
_max: { views: true },
});
// Result:
// {
// _count: 100,
// _avg: { views: 45.5 },
// _sum: { views: 4550 },
// _min: { createdAt: Date },
// _max: { views: 1000 }
// }
Functions
| Function | Description | Applies to |
|---|---|---|
_count |
Count records | All fields, or true |
_avg |
Average value | Numeric fields |
_sum |
Sum of values | Numeric fields |
_min |
Minimum value | Comparable fields |
_max |
Maximum value | Comparable fields |
A selected _sum that leaves its type’s range does not fail the call: a
number sum is the provider’s double sum computed without intermediate
overflow, Infinity or -Infinity past the double range; an int sum past
253 is the provider’s total as the nearest double, so it loses precision like a
number sum (use s.bigInt() for exact sums past 253); and a bigint or
decimal sum is exact past the int64 range on every provider. The other
aggregates of the call are returned as usual. A groupBy having or orderBy
on such a sum compares and sorts that same total, Infinity past every finite
sum. MySQL has no infinite double, so its having cannot tell an infinite
number sum from ±Number.MAX_VALUE (its orderBy still can). A bigint or
int sum’s having operand is a JavaScript number, so past 2^53 it is not
exact: PostgreSQL and MySQL compare the number’s shortest decimal spelling
(2 ** 63 as 9223372036854776000), and SQLite compares the double itself.
orderBy stays exact.
Options
await client.post.aggregate({
where: { ... }, // Filter
orderBy: { ... }, // Sort (for cursor)
cursor: { ... }, // Pagination cursor
take: 100, // Limit records
skip: 0, // Offset
_count: true | { ... },
_avg: { field: true },
_sum: { field: true },
_min: { field: true },
_max: { field: true },
});
orderBy only decides which rows a page keeps. exist and an unpaged aggregate
or count (no take, skip or cursor) read the filtered table directly, so the
database can use its own fast paths and indexes; a paged aggregate reads its ordered
window first.