Question

Technologies:

javascript

Tolerim

15 days ago

How can I combine GroupBy and Sum functions in Prisma?

Given a Prisma schema for a MongoDB database that includes the orders model with fields such as id, totalAmount, and createdAt, I am attempting to group the data by date and obtain the aggregate sum on a monthly basis as follows:

{
  "january": 4567,
   "February": 7785
}

While referring to the documentation, I have attempted to use the groupBy() method provided by Prisma, but I have not been successful. Here's the code I've written:

const sales = await prisma.orders.groupBy({
    by: ["createdAt"],
    _sum: {
      totalAmount: true,
    },
    orderBy: { createdAt: "desc" },
  });

Can you help me identify the issue with my approach and suggest a solution?

Answers(1)

Accepted Answer

To groupby based on dates and give the sum on the basis of month, you can use the $dateToString aggregation function in MongoDB. Here's how you can modify your Prisma schema to achieve this:

model orders {
  id             String              @id @default(auto()) @map("_id") @db.ObjectId
  totalAmount    Int
  createdAt      DateTime            @db.Date
  month          String
}

Then, you can use the following code to groupby based on month and give the sum:

const sales = await prisma.orders.groupBy({
    by: [
      {
        month: {
          $dateToString: {
            format: "%B",
            date: "$createdAt",
          },
        },
      },
    ],
    _sum: {
      totalAmount: true,
    },
    orderBy: { month: "desc" },
  });

In this code, we have added a new month field to the orders model. Then, we are using the $dateToString function to convert the createdAt field to a string in the format of the full month name (e.g. "January", "February", etc.) and storing it in the month field. Finally, we are grouping by the month field and using the _sum aggregation operator to calculate the total amount for each month. We are also ordering the results by month in descending order.