MongoDB Aggregate operation of sum of multiple columns

Question

I have this collection in MongoDB:

{ "_id" : ObjectId("5f1c0360235e252a40a5c7a0"), "DEPTS" : "4", "CONVENTIONS" : 2, "GIFTS" : 6, "MONEY" : 60 }
{ "_id" : ObjectId("5f1c0360235e252a40a5c7a1"), "DEPTS" : "1", "CONVENTIONS" : 7, "GIFTS" : 9, "MONEY" : 4 }
{ "_id" : ObjectId("5f1c0360235e252a40a5c7a2"), "DEPTS" : "5", "CONVENTIONS" : 3, "GIFTS" : 6, "MONEY" : 61 }
{ "_id" : ObjectId("5f1c0360235e252a40a5c7a3"), "DEPTS" : "6", "CONVENTIONS" : 3, "GIFTS" : 9, "MONEY" : 34 }
{ "_id" : ObjectId("5f1c0360235e252a40a5c7a4"), "DEPTS" : "5", "CONVENTIONS" : 3, "GIFTS" : 4, "MONEY" : 35 }
{ "_id" : ObjectId("5f1c0360235e252a40a5c7a5"), "DEPTS" : "1", "CONVENTIONS" : 8, "GIFTS" : 4, "MONEY" : 19 }
{ "_id" : ObjectId("5f1c0360235e252a40a5c7a6"), "DEPTS" : "6", "CONVENTIONS" : 5, "GIFTS" : 5, "MONEY" : 95 }
{ "_id" : ObjectId("5f1c0360235e252a40a5c7a7"), "DEPTS" : "7", "CONVENTIONS" : 8, "GIFTS" : 8, "MONEY" : 19 }
{ "_id" : ObjectId("5f1c0360235e252a40a5c7a8"), "DEPTS" : "5", "CONVENTIONS" : 1, "GIFTS" : 8, "MONEY" : 20 }
{ "_id" : ObjectId("5f1c0360235e252a40a5c7a9"), "DEPTS" : "6", "CONVENTIONS" : 6, "GIFTS" : 8, "MONEY" : 42 }
{ "_id" : ObjectId("5f1c0360235e252a40a5c7aa"), "DEPTS" : "3", "CONVENTIONS" : 3, "GIFTS" : 6, "MONEY" : 76 }
{ "_id" : ObjectId("5f1c0360235e252a40a5c7ab"), "DEPTS" : "8", "CONVENTIONS" : 3, "GIFTS" : 10, "MONEY" : 66 }
{ "_id" : ObjectId("5f1c0360235e252a40a5c7ac"), "DEPTS" : "10", "CONVENTIONS" : 6, "GIFTS" : 9, "MONEY" : 82 }
{ "_id" : ObjectId("5f1c0360235e252a40a5c7ad"), "DEPTS" : "3", "CONVENTIONS" : 4, "GIFTS" : 8, "MONEY" : 72 }
{ "_id" : ObjectId("5f1c0360235e252a40a5c7ae"), "DEPTS" : "8", "CONVENTIONS" : 10, "GIFTS" : 10, "MONEY" : 33 }
{ "_id" : ObjectId("5f1c0360235e252a40a5c7af"), "DEPTS" : "10", "CONVENTIONS" : 7, "GIFTS" : 5, "MONEY" : 68 }
{ "_id" : ObjectId("5f1c0360235e252a40a5c7b0"), "DEPTS" : "3", "CONVENTIONS" : 5, "GIFTS" : 9, "MONEY" : 89 }
{ "_id" : ObjectId("5f1c0360235e252a40a5c7b1"), "DEPTS" : "9", "CONVENTIONS" : 3, "GIFTS" : 2, "MONEY" : 44 }
{ "_id" : ObjectId("5f1c0360235e252a40a5c7b2"), "DEPTS" : "5", "CONVENTIONS" : 4, "GIFTS" : 6, "MONEY" : 99 }
{ "_id" : ObjectId("5f1c0360235e252a40a5c7b3"), "DEPTS" : "7", "CONVENTIONS" : 7, "GIFTS" : 7, "MONEY" : 17 }

I just want a single row for each department (it is represented by the name of the DEPTS column) with the sum of the values of all the columns.

For the sum in a single column I do:

db.depts.aggregate([ { $group: { _id: '$DEPTS', total: { $sum: '$MONEY' }}  }, {$out: "depts"} ])

with this result:

{ "_id" : "1", "total" : 431 }
{ "_id" : "2", "total" : 442 }
{ "_id" : "8", "total" : 402 }
{ "_id" : "4", "total" : 292 }
{ "_id" : "7", "total" : 548 }
{ "_id" : "3", "total" : 445 }
{ "_id" : "6", "total" : 872 }
{ "_id" : "9", "total" : 351 }
{ "_id" : "10", "total" : 525 }
{ "_id" : "5", "total" : 585 }

How can I do if I wanna the sum of CONVENTIONS, GIFTS and MONEY columns?

Like this:

{ "_id" : "1", "totalConv" : *value*, "totalGifts" : *value*, "total" : 431}
{ "_id" : "2", "totalConv" : *value*, "totalGifts" : *value*, "total" : *value*}
{ "_id" : "8", "totalConv" : *value*, "totalGifts" : *value*, "total" : 431}
etc.

Does this answer your question: https://stackoverflow.com/questions/58955059/sum-all-values-of-same-named-fields-of-docs-in-a-collection/58955411#58955411 ? — mickl, Jul 25 '20 at 10:38

score 1 · Answer 1 · answered Jul 25 '20 at 11:07

Try this code

db.collection.aggregate([
  {
    $group: {
      _id: "$DEPTS",
      total: {
        $sum: "$MONEY"
      },
      totalConv: {
        $sum: "$CONVENTIONS"
      },
      totalGifts: {
        $sum: "$GIFTS"
      }
    }
  }
])

mongoplayground

score 1 · Answer 2 · answered Jul 25 '20 at 11:09

1

I Hope it will help you.

db.getCollection('table').aggregate([{$group: {
         _id: '$DEPTS',
        totalConventions:{ $sum: '$CONVENTIONS' },
        totalGifts:{ $sum: '$GIFTS' },
        totalMoney:{ $sum: '$MONEY' },
    }}]);

answered Jul 25 '20 at 11:09

Prakash Harvani

1,007
7
18

MongoDB Aggregate operation of sum of multiple columns

2 Answers2