CODEWITHSUNDEEP
javamongodbspring-bootaggregation-frameworkspring-data-mongodb
474gaurav
474gaurav

Reputation: 99

How should I calculate sum of field grouped by another one using Spring Data MongoDB

I've a collection Users which looks something like this-

{ "post": "teacher",
  "salary": 4500
},
{ "post": "teacher",
  "salary": 9000
},
{ "post": "principal",
  "salary": 7000
},
{ "post": "teacher",
  "salary": 4500
}

I want to calculate the total salary of all the teachers together and the same way for principals. So I want something which looks like

"teachers_salary": 18000
"principals_salary": 7000

I want to use aggregation but I'm not getting the desired output. It'd be really helpful if anyone of you can help me get a solution to this.

Upvotes: 2

Views: 1023

Answers (2)

krlittle
krlittle

Reputation: 173

Here is an example of aggregating using spring-data-mongodb.

Assuming a results model object of PostTotalSalary.class:

public class PostTotalSalary {
    private String post; // "teacher" or "principal"
    private Integer totalSalary;
}

We will create a GroupOperation to gather all documents with the same value with a key of "post"

GroupOperation groupByPostAndSumSalary = group("post")
  .sum("salary")
  .as("totalSalary");

We can now use spring-data-mongodb to create an aggregation and then map the results to your results model object (assuming a collection name of "posts"):

Aggregation aggregation = Aggregation.newAggregation(groupByPostAndSumSalary);
AggregationResults<PostTotalSalary> groupResults = mongoTemplate.aggregate(aggregation, "posts", PostTotalSalary.class);

If you then want a list, AggregationResults has the getMappedResults method to do this:

List<PostTotalSalary> mappedResults = groupResults.getMappedResults();

The end result will be

[
    {
        "post" : "teacher",
        "totalSalary" : "18000" // the total of all salaries for every "teacher" document
    },
    {
        "post" : "principal",
        "totalSalary" : "7000" // the total of all salaries for every "principal" document 
    }
]

Upvotes: 1

Pasindu Gamarachchi
Pasindu Gamarachchi

Reputation: 566

On the mongoshell you can try this


db.collection_name.aggregate([ { 
    $group: { 
        _id: "$post", 
        totalsalary: { $sum: "$salary" }
    } 
} ] )

https://docs.mongodb.com/manual/reference/operator/aggregation/sum/#mongodb-group-grp.-sum

Upvotes: 1

Related Questions