CODEWITHSUNDEEP
mongodb
Hassan Ahamed
Hassan Ahamed

Reputation: 45

Sum of two grouped values in mongodb?

Trans_Id      Account_Id     Amount   Tran_Type
   1             HA1001       1000       C
   2             HA1001         50       D
   3             HA1001        100       C
   4             HA1001        400       D

Now how can I calculate balance amount in my account like sum of (C) - sum of (D) = 650 ?

Upvotes: 2

Views: 54

Answers (1)

chridam
chridam

Reputation: 103335

The tenary $cond operator will come in handy for this aggregation operation type. You can use it to calculate the sum given the Tran_Type value, so you could inject this logic within the $group pipeline stage where you will group all the documents (i.e. group by the null key). You would also need to pipe a $project operator to calculate the difference with the $subtract arithmetic operator.

The following example will demonstrate this concept. Run the pipeline operation to get the desired result:

Populate Test Documents: 

db.test.insert([
    {
        "Trans_Id": 1,
        "Account_Id": "HA1001",
        "Amount": 1000,
        "Tran_Type": "C"
    },
    {
        "Trans_Id": 2,
        "Account_Id": "HA1001",
        "Amount": 50,
        "Tran_Type": "D"
    },
    {
        "Trans_Id": 3,
        "Account_Id": "HA1001",
        "Amount": 100,
        "Tran_Type": "C"
    },
    {
        "Trans_Id": 4,
        "Account_Id": "HA1001",
        "Amount": 400,
        "Tran_Type": "D"
    }
])

Aggregation Pipeline:

db.test.aggregate([
    {
        "$group": {
            "_id": null,
            "total_C": {
                "$sum": {
                    "$cond": [ 
                        { "$eq": [ "$Tran_Type", "C" ] }, 
                        "$Amount", 0 
                    ]
                }
            },
            "total_D": {
                "$sum": {
                    "$cond": [ 
                        { "$eq": [ "$Tran_Type", "D" ] }, 
                        "$Amount", 0 
                    ]
                }
            }
        }
    },
    {
        "$project": {
            "balance": {
                "$subtract": ["$total_C", "$total_D"]
            }, "_id": 0
        }
    }
])

Sample Output:

{
    "result" : [ 
        { "balance" : 650 }
    ],
    "ok" : 1
}

Upvotes: 1

Related Questions