# It's Prisma Time - Aggregate and GroupBy

> Welcome back Folks 👋 Today we are seeing how to aggregate and group data. Let's start from the...

- **Author**: Luca Del Puppo (@puppo92) — AI Native Engineer at Nearform
- **Published**: Jan 18, 2022
- **Canonical URL**: https://delpuppo.net/blog/it-s-prisma-time/its-prisma-time-aggregate-and-groupby/
- **Series**: It's Prisma Time (Part 11 of 16) — https://delpuppo.net/blog/series/it-s-prisma-time/
- **Series LLMs.txt**: https://delpuppo.net/blog/series/it-s-prisma-time/llms.txt
- **Category**: It's Prisma Time
- **Tags**: javascript, typescript, database, orm
- **Hashnode Mirror**: https://blog.delpuppo.net/its-prisma-time-aggregate-and-groupby
- **Dev.to Mirror**: https://dev.to/playfulprogramming/its-prisma-time-aggregate-and-groupby-36a7
- **Blog Index LLMs.txt**: https://delpuppo.net/blog/llms.txt

## Series Context: It's Prisma Time

- Part 1: [It's Prisma Time - Introduction](https://delpuppo.net/blog/it-s-prisma-time/its-prisma-time-introduction/)
- Part 2: [It's Prisma Time - Setup](https://delpuppo.net/blog/it-s-prisma-time/its-prisma-time-setup/)
- Part 3: [It's Prisma Time - Create Table](https://delpuppo.net/blog/it-s-prisma-time/its-prisma-time-create-table/)
- Part 4: [It's Prisma Time - Create Db](https://delpuppo.net/blog/it-s-prisma-time/its-prisma-time-create-db/)
- Part 5: [It's Prisma Time - Create Relations](https://delpuppo.net/blog/it-s-prisma-time/its-prisma-time-create-relations/)
- Part 6: [It's Prisma Time - Insert](https://delpuppo.net/blog/it-s-prisma-time/its-prisma-time-insert/)
- Part 7: [It's Prisma Time - Delete](https://delpuppo.net/blog/it-s-prisma-time/its-prisma-time-delete/)
- Part 8: [It's Prisma Time - Update](https://delpuppo.net/blog/it-s-prisma-time/its-prisma-time-update/)
- Part 9: [It's Prisma Time - Select](https://delpuppo.net/blog/it-s-prisma-time/its-prisma-time-select/)
- Part 10: [It's Prisma Time - Pagination](https://delpuppo.net/blog/it-s-prisma-time/its-prisma-time-pagination/)
- Part 11: [It's Prisma Time - Aggregate and GroupBy](https://delpuppo.net/blog/it-s-prisma-time/its-prisma-time-aggregate-and-groupby/) **(Current Article)**
- Part 12: [It's Prisma Time - Transactions](https://delpuppo.net/blog/it-s-prisma-time/its-prisma-time-transactions/)
- Part 13: [It's Prisma Time - Logging](https://delpuppo.net/blog/it-s-prisma-time/its-prisma-time-logging/)
- Part 14: [It's Prisma Time - Execute your own queries](https://delpuppo.net/blog/it-s-prisma-time/its-prisma-time-execute-your-own-queries/)
- Part 15: [It's Prisma Time - Migrations](https://delpuppo.net/blog/it-s-prisma-time/its-prisma-time-migrations/)
- Part 16: [It's Prisma Time - Seeding](https://delpuppo.net/blog/it-s-prisma-time/its-prisma-time-seeding/)

---

Welcome back Folks 👋 
Today we are seeing how to aggregate and group data.

Let's start from the first one.
To aggregate data in Prisma you have to use the [aggregate](https://www.prisma.io/docs/reference/api-reference/prisma-client-reference#aggregate) method.
Using this method, you can aggregate data of type number, and you can do these operations:
- max
- min
- count
- sum
- avg

_N.B. I won't spend time explaining what these operations do because I think their names speak for themselves_

Therefore, let's give an example to see the aggregate method at work.
```ts
const aggregate = await prisma.author.aggregate({
  _avg: {
    age: true,
  },
  _max: {
    age: true,
  },
  _min: {
    age: true,
  },
});
```
As you can see, in this example, we aggregated the authors and we got as result the authors' ages average, the age of the older author and the age of the younger author. The result is composed in this way:
```ts
{
    _avg: {
        age: true;
    };
    _max: {
        age: true;
    };
    _min: {
        age: true;
    };
}
```
In addition, the aggregate method allows us to use these operations:
- where
- orderBy
- take
- skip 
- cursor

So as you can immagine, you can filter and sort your data before aggregate them.
I think this feature might be clear that way, so let's move on to the groupBy feature.

The [groupBy](https://www.prisma.io/docs/reference/api-reference/prisma-client-reference#groupby) feature allows us to group data by one o more fields.
Let’s see an example to start
```ts
const commentsGroupByPost = await prisma.comment.groupBy({
  by: ["postId"],
  _count: {
    authorId: true,
    _all: true,
  },
  orderBy: {
    _count: {
      authorId: "desc",
    },
  },
  having: {
    authorId: {
      _count: {
        gt: 1,
      },
    },
  },
});
```
In this example, we grouped the comments by post, and after that, we counted the number of comments with the author and the number of comments for that post.
Moreover, we sorted the result by the authors' count and, using the having option, we took only the posts that have at least a comment with an author.
As you can see, this method is not difficult to use and understand. _It's also possibile to filter the data with the `where` option._

I think for today that's all.
We saw how to aggregate and how to group data using Prisma and, if you want to deepen, the prisma team created this [video](https://www.youtube.com/watch?v=BdlCPdPaorY) to explain better the groupBy feature.

In the next article we will see how to run our queries under transaction, but for today it's time to close 😃 

See you soon guys 
Bye bye 👋 

_To show the code of this article in action follow this [in the GitHub repository](https://github.com/puppo/it-s-prisma-time/tree/11-grouping-and-aggregate)_