Prisma Aggregations — Counting, Summing, and Grouping Data
In this tutorial, you will learn about Prisma Aggregations. We cover key concepts, practical examples, and best practices to help you master this topic.
Prisma aggregations provide built-in aggregate functions -- count, sum, avg, min, max -- and groupBy for grouping and aggregating data, enabling efficient data analysis without raw SQL.
What You'll Learn
By the end of this lesson you will use aggregate functions on models, group records by fields, filter grouped results with having, aggregate on related models, and combine with pagination.
Why It Matters
Aggregations power dashboards, analytics, and reporting features. Summing sales, counting users, averaging ratings, and finding min/max values are essential for data-driven applications.
Real-World Use
DodaZIP's admin dashboard uses Prisma aggregations: count of active users, sum of storage used, average processing time, and files grouped by status. These queries run in milliseconds against millions of records.
Aggregate Functions
Use count, sum, avg, min, and max.
import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient();
// Basic count
const totalUsers = await prisma.user.count();
console.log('Total users:', totalUsers);
// Count with filter
const activeUsers = await prisma.user.count({
where: { status: 'ACTIVE' },
});
// Aggregation
const stats = await prisma.file.aggregate({
_count: { id: true },
_sum: { sizeBytes: true },
_avg: { sizeBytes: true },
_min: { sizeBytes: true },
_max: { sizeBytes: true },
where: { user_id: userId },
});
console.log('File stats:', {
count: stats._count.id,
totalSize: stats._sum.sizeBytes,
avgSize: stats._avg.sizeBytes,
minSize: stats._min.sizeBytes,
maxSize: stats._max.sizeBytes,
});
# aggregate_functions.py
# Available aggregate functions
def aggregate_funcs():
funcs = {
"_count": "Count of records",
"_sum": "Sum of numeric field values",
"_avg": "Average of numeric field values",
"_min": "Minimum value of a field",
"_max": "Maximum value of a field",
}
print("Aggregate Functions:")
for func, desc in funcs.items():
print(f" {func:10s} | {desc}")
print()
print("Usage:")
print(" prisma.model.aggregate({")
print(" _count: { field: true },")
print(" _sum: { field: true },")
print(" _avg: { field: true },")
print(" where: { ... }")
print(" })")
aggregate_funcs()
GroupBy
Group records by field values for analysis.
// Group files by status
const filesByStatus = await prisma.file.groupBy({
by: ['status'],
_count: { id: true },
_sum: { sizeBytes: true },
_avg: { sizeBytes: true },
});
console.log('Files by status:');
filesByStatus.forEach((group) => {
console.log(` ${group.status}: ${group._count.id} files`);
});
// Group with sorting
const groupResult = await prisma.user.groupBy({
by: ['plan'],
_count: { id: true },
_sum: { storageUsed: true },
orderBy: { _count: { id: 'desc' } },
});
// Group with having (filter grouped results)
const groupsWithCondition = await prisma.file.groupBy({
by: ['status'],
_count: { id: true },
having: {
id: {
_count: { gt: 10 },
},
},
});
# group_by.py
# GroupBy patterns
def group_by():
print("GroupBy Patterns:")
print()
print("Basic group:")
print(" prisma.model.groupBy({")
print(" by: ['field'],")
print(" _count: { id: true }")
print(" })")
print()
print("Multiple aggregates:")
print(" prisma.model.groupBy({")
print(" by: ['status'],")
print(" _count: { id: true },")
print(" _sum: { amount: true },")
print(" _avg: { amount: true }")
print(" })")
print()
print("With having (filter groups):")
print(" having: {")
print(" id: { _count: { gt: 10 } }")
print(" }")
group_by()
Aggregating on Relations
Perform aggregations across related models.
// Count related records
const users = await prisma.user.findMany({
include: {
_count: {
select: { posts: true, comments: true },
},
},
});
users.forEach((user) => {
console.log(`${user.name}: ${user._count.posts} posts`);
});
// Aggregate on relation fields
const filesWithUserStats = await prisma.user.findMany({
select: {
id: true,
email: true,
_count: { select: { files: true } },
files: {
select: {
_sum: { sizeBytes: true },
_avg: { sizeBytes: true },
},
},
},
where: { files: { some: {} } },
});
# relation_aggs.py
# Aggregating on relations
def relation_aggregations():
print("Relation Aggregations:")
print()
print("Count related records:")
print(" prisma.user.findMany({")
print(" include: {")
print(" _count: {")
print(" select: { posts: true }")
print(" }")
print(" }")
print(" })")
print()
print("Nested aggregation:")
print(" prisma.user.findMany({")
print(" include: {")
print(" posts: {")
print(" select: {")
print(" _count: { comments: true }")
print(" }")
print(" }")
print(" }")
print(" })")
relation_aggregations()
Combining with Filtering
Apply filters to aggregation queries.
// Filter before aggregation
const monthlyStats = await prisma.file.aggregate({
_count: true,
_sum: { sizeBytes: true },
where: {
createdAt: {
gte: new Date('2026-01-01'),
lt: new Date('2026-02-01'),
},
},
});
// GroupBy with filtering
const statusBreakdown = await prisma.file.groupBy({
by: ['status'],
_count: { id: true },
_sum: { sizeBytes: true },
where: {
user: { plan: 'PREMIUM' },
createdAt: { gte: thirtyDaysAgo },
},
orderBy: { _count: { id: 'desc' } },
});
// Aggregate with distinct count
const distinctUsers = await prisma.file.aggregate({
_count: {
userId: true, // This counts total files
},
// For distinct count, use groupBy instead
});
// Distinct users via groupBy
const distinctUserIds = await prisma.file.groupBy({
by: ['userId'],
_count: { id: true },
});
# filter_aggregations.py
# Filtering with aggregations
def filter_aggs():
print("Filtering with Aggregations:")
print()
print("Filter before aggregate:")
print(" prisma.model.aggregate({")
print(" _count: true,")
print(" _sum: { field: true },")
print(" where: { createdAt: { gte: startDate } }")
print(" })")
print()
print("Filtered groupBy:")
print(" prisma.model.groupBy({")
print(" by: ['field'],")
print(" _count: true,")
print(" where: { status: 'ACTIVE' }")
print(" })")
print()
print("Note: count = true returns total count")
print("count: { field: true } returns per-field count")
filter_aggs()
Common Mistakes
Not filtering before aggregation: Aggregating over all records can be slow on large tables. Always add a where filter when possible.
Forgetting to handle null aggregates: _sum, _avg return null when no records match. Use null coalescing:
stats._sum.field ?? 0.Using count for distinct counts: count counts all matching records. For distinct counts, use groupBy + count of groups.
Not indexing aggregated fields: Aggregation queries scan the table without appropriate indexes. Add indexes on filtered and grouped columns.
Over-aggregating in application code: Let the database do the aggregation. Loading all records and aggregating in code is slow and wasteful.
Practice Questions
What aggregate functions does Prisma support? _count, _sum, _avg, _min, _max.
How do you group records by a field? Use prisma.model.groupBy({ by: ['field'], _count: true }).
How do you filter grouped results (HAVING equivalent)? Use the
havingparameter in groupBy.How do you count related records? Include
_count: { select: { relationName: true } }in the query.Challenge: Create a dashboard query that returns monthly file upload statistics: count of uploads, total storage, average file size, and breakdown by file type.
FAQ
Mini Project
Create an analytics service that provides daily, weekly, and monthly file processing statistics using Prisma aggregations.
def analytics_service():
print("Analytics Service Queries:")
print()
print("Daily stats:")
print(" prisma.file.groupBy({")
print(" by: ['status'],")
print(" _count: { id: true },")
print(" _sum: { sizeBytes: true },")
print(" where: {")
print(" createdAt: { gte: todayStart, lt: tomorrowStart }")
print(" }")
print(" })")
print()
print("Processing time stats:")
print(" prisma.processingJob.aggregate({")
print(" _avg: { durationMs: true },")
print(" _min: { durationMs: true },")
print(" _max: { durationMs: true },")
print(" _count: true,")
print(" where: { status: 'COMPLETED' }")
print(" })")
analytics_service()
What's Next
Next: Prisma Middleware for hooks and interceptors.
Built by the developers of DodaTech
Doda Browser, DodaZIP & Durga Antivirus Pro