I have payments collection(table) and data have based on branches payment..
branch details have in branch collections.
Sample data in payment collections:
[
{
"amount" : 1000,
"paymentStatus" : "Unpaid",
"branchId" : ObjectId("5b320de5a79ae855912eb332"),
"userId" : ObjectId("5b320de5a79ae855912eb333"),
"receivedAmount" : 500,
},
{
"amount" : 5000,
"paymentStatus" : "paid",
"branchId" : ObjectId("5b320de5a79ae855912e3335"),
"userId" : ObjectId("5b320de5a79443855912eb332"),
"receivedAmount" : 5000,
},
{
"amount" : 2000,
"paymentStatus" : "paid",
"branchId" : ObjectId("5b320de5a79ae855912eb338"),
"userId" : ObjectId("5b320de5a79ae855912eb432"),
"receivedAmount" : 2000,
}
]
I am trying to fetch data based on below conditions
- group by branchId,
- Total Amount
- Total receivedAmount,
- total Users
- Total Paid users count.
- branch details
my query:
> db.payments.aggregate([ { $group: { _id : { branchId : '$branchId'}, amount: {$sum: '$amount'}, receivedAmount : {$sum : '$receivedAmount'}, totalStudents: {$sum :1} }}])
current output:
[{ "_id" : { "branchId" : ObjectId("5b320de5a79ae855912eb377") }, "amount" : 65148, "receivedAmount" : 13276, "totalStudents" : 33 }
{ "_id" : { "branchId" : ObjectId("5a992ae104329d5359be6980") }, "amount" : 9440, "receivedAmount" : 0, "totalStudents" : 4 }
]
expected output:
[{ "branchId" : ObjectId("5b320de5a79ae855912eb377") , "amount" : 65148, "receivedAmount" : 13276, "totalStudents" : 33, "paidStudents": 2 , "branchName": 'abcd'}
{ "_id" : { "branchId" : ObjectId("5a992ae104329d5359be6980") , "amount" : 9440, "receivedAmount" : 0, "totalStudents" : 4, "paidStudents": 2 , "branchName": 'xyz'}
]
Questions, how to fetch count only for paid status and join to branch tables to get branch name.