How to count and sum a field between 2 dates in MongoDB?

To count and sum a field between 2 dates in MongoDB, use the aggregation pipeline with $match to filter documents by date range using $gte and $lte, then $group with $sum to calculate totals.

Syntax

db.collection.aggregate([
    {
        $match: {
            dateField: {
                $gte: ISODate("start-date"),
                $lte: ISODate("end-date")
            }
        }
    },
    {
        $group: {
            _id: null,
            SUM: { $sum: "$fieldToSum" },
            COUNT: { $sum: 1 }
        }
    }
]);

Sample Data

db.countandsumdemo.insertMany([
    {"Value": 10, "created_at": ISODate('2019-10-11')},
    {"Value": 50, "created_at": ISODate('2019-01-31')},
    {"Value": 100, "created_at": ISODate('2019-07-01')}
]);

{
    "acknowledged": true,
    "insertedIds": [
        ObjectId("5e038e6df5e889d7a51994fa"),
        ObjectId("5e038e77f5e889d7a51994fb"),
        ObjectId("5e038e8af5e889d7a51994fc")
    ]
}

View Sample Data

db.countandsumdemo.find();

{
    "_id": ObjectId("5e038e6df5e889d7a51994fa"),
    "Value": 10,
    "created_at": ISODate("2019-10-11T00:00:00Z")
}
{
    "_id": ObjectId("5e038e77f5e889d7a51994fb"),
    "Value": 50,
    "created_at": ISODate("2019-01-31T00:00:00Z")
}
{
    "_id": ObjectId("5e038e8af5e889d7a51994fc"),
    "Value": 100,
    "created_at": ISODate("2019-07-01T00:00:00Z")
}

Example: Count and Sum Between May 1 and December 31, 2019

db.countandsumdemo.aggregate([
    {
        $match: {
            created_at: {
                $gte: ISODate('2019-05-01'),
                $lte: ISODate('2019-12-31')
            }
        }
    },
    {
        $group: {
            _id: null,
            SUM: { $sum: "$Value" },
            COUNT: { $sum: 1 }
        }
    }
]);

{ "_id": null, "SUM": 110, "COUNT": 2 }

Key Points

• $gte (greater than or equal) and $lte (less than or equal) define the date range.
• $sum: "$fieldName" sums the field values, while $sum: 1 counts documents.
• _id: null groups all matching documents into a single result.

Conclusion

Use MongoDB's aggregation pipeline with $match for date filtering and $group with $sum to efficiently count and sum field values within any date range.