I have a collection billow and i need to find date wise total cost and sum of all cost available in this collection. I can find total cost of a day but failed to get sum of all cost from the collection

[{
        "date":"12-2-2015",
        "cost":100
    },
    {
        "date":"13-2-2015",
        "cost":10
    },
    {
        "date":"12-2-2015",
        "cost":40
    },
    {
        "date":"13-2-2015",
        "cost":30
    },
    {
        "date":"13-2-2015",
        "cost":80
    }]

I can find output like

    [{
        "day": "12-2-2015",
        "cost": 140
    },{
        "day": "13-2-2015",
        "cost": 120
    }]

But I want output like this.

{
   "day": "12-2-2015",
   "cost": 140,
   "total": 260
 }

CodePudding user response：

use this aggregate I dont add $match stage you could add to match date

db.collection.aggregate([
  {
    $group: {
      _id: null,
      orig: {
        $push: "$$ROOT"
      },
      "total": {
        $sum: "$cost"
      },
      
    }
  },
  {
    $unwind: "$orig"
  },
  {
    $project: {
      date: "$orig.date",
      cost: "$orig.cost",
      total: "$total"
    }
  },
  {
    $group: {
      _id: "$date",
      cost: {
        $sum: "$cost"
      },
      orig: {
        $push: "$$ROOT.total"
      }
    },
    
  },
  {
    "$unwind": "$orig"
  },
  {
    $group: {
      _id: {
        _id: "$_id",
        cost: "$cost",
        total: "$orig"
      },
      
    },
    
  },
  {
    $project: {
      date: "$_id._id",
      "cost": "$_id.cost",
      total: "$_id.total",
      _id: 0
    }
  }
])

https://mongoplayground.net/p/eN-pDg2Zz7u

CodePudding user response：

It is like 2 queries.

There are 3 solutions that i can think of

• 2 queries (works no matter the collection size)
• 1 query and facet (the bellow solution)
  group and pack each group in an array (limitation = ngroups(distinct day dates) small enough to fit in 1 array 16MB distinct dates, (which is true for like 200.000? distinct days see this)
• 1 query no facet
  for example group and pack all collection into 1 array (limitation = all collection must fit in 100MB memory because of $push see this)

*for the limits i think they are like that, based on what i have understanded.

Query

Test code here

db.collection.aggregate([
  {
    "$facet": {
      "total": [
        {
          "$group": {
            "_id": null,
            "total": {
              "$sum": "$cost"
            }
          }
        }
      ],
      "coll": [
        {
          "$group": {
            "_id": "$date",
            "cost": {
              "$sum": "$cost"
            }
          }
        }
      ]
    }
  },
  {
    "$unwind": {
      "path": "$coll"
    }
  },
  {
    "$project": {
      "total": {
        "$let": {
          "vars": {
            "t": {
              "$arrayElemAt": [
                "$total",
                0
              ]
            }
          },
          "in": "$$t.total"
        }
      },
      "date": "$coll._id",
      "cost": "$coll.cost"
    }
  }
])

CodePudding user response：

I would do one query to get a cursor, then iterate the cursor and at the same time sum the total cost and push the relevant doc, then add the total to each group. In this way you perform only one query to mongodb and let your server do the rest while keeping the code simple.

// 1. Fetch the groups
const grouped = db.data.aggregate([
    { $group: { 
        _id: "$date",
        cost: { $sum: "$cost" }
    }}
]);

// 2. Iterate the cursor, push the results into an array while summing the total cost
let total = 0;
const result = [];
grouped.forEach(group => {
    total  = group.cost;
    result.push(group); // push as much as your limit
});

// 3. Add total to each group
result.forEach(group => group.total = total);