How can I get a sum of sums using MongoDB aggregation?

user_598683 0
Viewed 227

I would like to get the total (sum) of the totalHoursForYear value. Here is my current aggregation:

const byYear = await this.workHistoryModel.aggregate([
  {
    $group: {
      _id: '$year',
      history: {
        $push: '$$ROOT'
      },
      totalHoursForYear: {
        $sum: {
          $add: [
            { $multiply: ['$eightHourDays', 8] },
            { $multiply: ['$tenHourDays', 10] },
            { $multiply: ['$twelveHourDays', 12] },
          ]
        }
      },
    }
  },
]);

Now I need to get a sum of the totalHoursForYear to get the total for all years. Is this possible? I can't seem to get the syntax correct. When I attempt to add another sum:

$group: {
  ...,
  totalHours: {
    $sum: {
      $add: '$totalHoursForYear'
    },
    $push: '$$ROOT'
  }
}

I get an error: "The field 'totalHours' must specify one accumulator"

aggregation-framework mongodb
1 Answers

When you need total sum, you can do another $group with _id:null which means to consider all documents

With your script, add the following to get the total and get the same structure.

{
    $group: {
      _id: null,
      data: {
        $push: "$$ROOT"
      },
      total: {
        $sum: "$totalHoursForYear"
      }
    }
  },
  {
    "$unwind": "$data"
  },
  {
    "$addFields": {
      _id: "$data._id",
      history: "$data.history",
      totalHoursForYear: "$data.totalHoursForYear",
      _id: "$$REMOVE",
      data: "$$REMOVE"
    }
  }

Working Mongo playground

user_79757710
Related