MongoDB.live, free & fully virtual. June 9th - 10th. Register Now

How to do group on multiple columns and summing up individual columns

Hi Friends,
I am new to mongoDB and it’s really exciting.

I have a scenario where i need to group on common fields(3) span across collections and have to perform summing up all the numeric columns by column wise and all string/text columns need to make “Nan” or “Null”.

Help me to form the query…
attaching two sample collections, great regards from my side.

The common fields across the collections are, X, Y_DT and Z. and other columns either numeric, string/text.

Hi @Murali_Muppireddy, you would want to do something similar to the following:

{
    $group: {
        "_id": {
            X: "$X",
            Y_DT: "$Y_DT",
            Z: "$Z"
        },
        adj: {"$sum": "$adj"},
        bjc: {"$sum": "$bjc"},
        ...
    }
} 

Any non numeric value will be converted to 0 for the purposes of the $sum operation.

1 Like

Thank @Doug_Duncan, I could able to get the expected results…
with the following query…

 db.test650.aggregate(
{
    $group: {
        "_id": {
            X: "$X",
            Y_DT: "$Y_DT",
			Z: "$Z"
         },
		adj: {$sum: "$adj" },   
		bjc: {$sum: "$bjc" },
		jbc: {$sum: "$jbc" },
		mnk: {$sum: "$mnk"}
    }
 }
)

But in case of bigger collection with many number of fields, say like 500, it will be very complex to write all the filed names and sumup, is there any generic way we can write some thing like looping rest of the fields(other than grouping ones)?