Using MongoDB nested group and sum to get the count of stocks with similar ProductID?

To count stocks with similar ProductID using nested $group and $sum in MongoDB, group documents by ProductName and use $addToSet with $size to count unique ProductIDs per group.

Syntax

db.collection.aggregate([
    {
        $group: {
            _id: { "fieldName": "$fieldName" },
            "sumField": { $sum: "$field" },
            "productIds": { $addToSet: "$ProductId" }
        }
    },
    {
        $project: {
            "fieldName": true,
            "sumField": true,
            "stockCount": { $size: "$productIds" }
        }
    }
]);

Sample Data

db.demo466.insertMany([
    {
        "ProductPrice": 150,
        "ProductQuantity": 1,
        "ProductName": "Product-1",
        "ActualAmount": 110,
        "ProductProfit": 40,
        "ProductId": 1
    },
    {
        "ProductPrice": 150,
        "ProductQuantity": 1,
        "ProductName": "Product-1",
        "ActualAmount": 110,
        "ProductProfit": 40,
        "ProductId": 2
    },
    {
        "ProductPrice": 170,
        "ProductQuantity": 2,
        "ProductName": "Product-2",
        "ActualAmount": 130,
        "ProductProfit": 50,
        "ProductId": 3
    }
]);

{
    "acknowledged": true,
    "insertedIds": [
        ObjectId("5e80477cb0f3fa88e2279066"),
        ObjectId("5e80477db0f3fa88e2279067"),
        ObjectId("5e80477eb0f3fa88e2279068")
    ]
}

View Sample Data

db.demo466.find();

{ "_id": ObjectId("5e80477cb0f3fa88e2279066"), "ProductPrice": 150, "ProductQuantity": 1, "ProductName": "Product-1", "ActualAmount": 110, "ProductProfit": 40, "ProductId": 1 }
{ "_id": ObjectId("5e80477db0f3fa88e2279067"), "ProductPrice": 150, "ProductQuantity": 1, "ProductName": "Product-1", "ActualAmount": 110, "ProductProfit": 40, "ProductId": 2 }
{ "_id": ObjectId("5e80477eb0f3fa88e2279068"), "ProductPrice": 170, "ProductQuantity": 2, "ProductName": "Product-2", "ActualAmount": 130, "ProductProfit": 50, "ProductId": 3 }

Example: Count Stocks with Similar ProductID

db.demo466.aggregate([
    {
        $group: {
            _id: {
                "ProductName": "$ProductName"
            },
            "ActualAmount": { $sum: "$ActualAmount" },
            "ProductQuantity": { $sum: "$ProductQuantity" },
            "ProductId": { $addToSet: "$ProductId" }
        }
    },
    {
        $project: {
            "ProductQuantity": true,
            "ActualAmount": true,
            "NumberOfProductInStock": { $size: "$ProductId" }
        }
    }
]);

{ "_id": { "ProductName": "Product-2" }, "ActualAmount": 130, "ProductQuantity": 2, "NumberOfProductInStock": 1 }
{ "_id": { "ProductName": "Product-1" }, "ActualAmount": 220, "ProductQuantity": 2, "NumberOfProductInStock": 2 }

How It Works

• $group groups documents by ProductName
• $sum calculates total ActualAmount and ProductQuantity
• $addToSet collects unique ProductId values
• $size counts the number of unique ProductIDs per group

Conclusion

Use nested $group with $addToSet and $size to count unique ProductIDs within each group. This approach effectively counts stocks with similar characteristics while maintaining unique identification.