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: stackoverflow.com/questions/58955059/… ? — mickl
– mickl, Commented Jul 25, 2020 at 10:38

Mayur Vaghasiya · Accepted Answer · 2020-07-25 11:07:21Z

1

Try this code

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

mongoplayground

answered Jul 25, 2020 at 11:07

Mayur Vaghasiya

1,4812 gold badges18 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Prakash Harvani · Accepted Answer · 2020-07-25 11:09:31Z

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, 2020 at 11:09

Prakash Harvani

1,0418 silver badges18 bronze badges

Collectives™ on Stack Overflow

MongoDB Aggregate operation of sum of multiple columns

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related