CODEWITHSUNDEEP
mongodb
Sharath
Sharath

Reputation: 2428

MongoDB Group by with summation of all columns

Sample Input:

[
  {
    "_id": 1,
    "team": "a",
    "score": 200
  },
  {
    "_id": 2,
    "team": "a",
    "score": 200
  },
  {
    "_id": 3,
    "team": "b",
    "score": 700
  },
  {
    "_id": 4,
    "team": "c",
    "score": 100
  }
]

From the above data I am trying to get summation of all score columns along with doing a group by on team.

In the sample input team a has 2 records but during calculation I need to take only 1 of the records (basically groupby on team).

So my final output should be 200 + 700 + 100 = 1000

I have tried with below query but I could not get desired output.

db.games.aggregate([ { 
$group: { 
    "_id": "$team",
    score : { $first: '$score' },
    total: { $sum: "$score" } 
}
}])

and output as

{
    "result" : [ 
        {
            "_id" : "c",
            "score" : 100,
            "total" : 100
        }, 
        {
            "_id" : "b",
            "score" : 700,
            "total" : 700
        }, 
        {
            "_id" : "a",
            "score" : 200,
            "total" : 400
        }
    ],
    "ok" : 1.0000000000000000
}

Upvotes: 3

Views: 461

Answers (1)

Maksim Simkin
Maksim Simkin

Reputation: 9679

I think you could do something like this:

db.names.aggregate([
        {$group:{_id:"$team", score: {$first:"$score"}}}, 
        {$group:{_id:"", sum: {$sum:"$score"}}}]);

Upvotes: 1

Related Questions