0

I have a collection named sales with documents structured as follows:-

{

"_id": ObjectId("..."),

"date": ISODate("2024-06-15T00:00:00Z"),

"store": "Store A",

"items": [

     {

         "name": "item1",

         "quantity": 5,

         "price": 10.0

     },

     {

         "name": "item2",

         "quantity": 3,

         "price": 20.0

     }

]
}

How do i calculate the total revenue generated by each store for each month, along with the average price of items sold. The result should be sorted first by store and then by month (in ascending order).

Expected Output:-

[

{

     "store": "Store A",

     "month": "2024-06",

     "totalRevenue": 230.0,

     "averagePrice": 15.0

},

{

     "store": "Store B",

     "month": "2024-06",

     "totalRevenue": 150.0,

     "averagePrice": 12.5

}

]

what i've tried:-

db.sales.aggregate([{
  "$group": {
    "_id" : null,
    "totalRevenue": {
      "$sum": {
        "$multiply": ["$quantity", "$price"]
      }
    },
  }
}]);

update:-

db.sales.aggregate([
  {
    $match: {
      date: { $gte: new ISODate("2024-06-01"), $lt: new ISODate("2024-06-30") },
    },
  },

  {
    $group: {
      month: { $dateToString: { format: "%Y-%m-%d", date: "$date" } },
      totalRevenue: { $sum: { $multiply: ["$price", "$quantity"] } },
      averagePrice: { $avg: "$price" },
    },
  },

  {
    $sort: { totalRevenue: -1 },
  },
]);
4
  • Change your grouping condition. The _id part shouldn't be null as that puts everything in a single bucket Commented Sep 9, 2024 at 20:40
  • Thanks.. what about filtering the data by month, diff stores? Commented Sep 10, 2024 at 4:30
  • Why do you group by months when you filter data of one month only. Commented Sep 10, 2024 at 5:38
  • If you want to group by both month and store, include both of them in the _id value for the $group. Commented Sep 11, 2024 at 17:40

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.