How to sum up one record per day for one month?

Working with Data
1

Hi
I’m trying to do to aggregate, sum up values in a month, but i only want to sum 1 entry per day, i tried to use $limit before the group but only get 1 entry return.

Thanks.

[
    { 
        $match: 
        {  
            "fPort": 60,
            "rxInfo.time":   
                {  
                    "$gte": "2020-12-01T00:00:00.000000Z",  
                    "$lte": "2020-12-31T59:59:59.935Z"
                }
        }
    },
    { $limit: '1' }, ///// This only returns 1 record and not 1 per day.
    { 
        $group: 
            {  
                _id: "9cd9cb00000124c1",
                "Total":   
                    {
                        "$sum": "$object.TotalDaily"  
                    }
            }
    }
]
1 Like
2

Hello @Q_Lion, Welcome to MongoDB Developer Community,

As i can understand your question, you need to get count of documents yearly or monthly or daily,

You can use $year, $month, $dayOfMinth Date Expression Operators.

Example for Daily document count,

[{
    $group: {
        _id: {
            year: { $year: "$yourDateFieldName" },
            month: { $month: "$yourDateFieldName" },
            day: { $dayOfMonth: "$yourDateFieldName" }
        },
        Total: { $sum: 1 }
    }
}]

Example for Monthly document count,

[{
    $group: {
        _id: {
            year: { $year: "$yourDateFieldName" },
            month: { $month: "$yourDateFieldName" }
        },
        Total: { $sum: 1 }
    }
}]

You can replace yourDateFieldName to your collection field, and put your $match stage before this $group stage.

1 Like
3

I think i have found the query.

i managed to get 1 record per day and sun the values for 1 month, Thanks

[
    { 
        $match: 
                {  
                    "fPort": 60,
                    "rxInfo.time":
                                    {
                                        "$gte": "2020-12-01T00:00:00.000000Z",
                                        "$lte": "2020-12-31T59:59:59.99"      
                                    }
                } 
    }, 
    { 
        $project:
                {   
                    _id:1,
                    year:
                            {
                                $year:  
                                        { 
                                            $dateFromString: 
                                                            {
                                                                dateString: 
                                                                            {"$arrayElemAt": [ "$rxInfo.time", 0 ] }
                                                            }
                                        }
                            },
                    month:
                            {
                                $month: 
                                        {
                                            $dateFromString: 
                                                            { 
                                                                dateString: 
                                                                            {"$arrayElemAt": [ "$rxInfo.time", 0 ] }
                                                            }
                                        }
                            },
                    day:
                            {
                                $dayOfMonth: 
                                        {
                                            $dateFromString: 
                                                            { 
                                                                dateString: 
                                                                            {"$arrayElemAt": [ "$rxInfo.time", 0 ] }
                                                            }
                                        }
                            },
                    sum : "$object.TotalDaily"
                } 
    }, 
    { 
        $group: 
                {  
                    _id:
                        {
                            year:"$year",
                            month:"$month",
                            day:"$day"
                        },
                    TotalPerDay:
                        {
                            $sum:"$sum"
                        }
                }
    }, 
    { 
        $group: 
                { 
                    _id: "9cd9cb00000124c1",
                    "MonthlyTotal":
                        {
                            "$sum": "$TotalPerDay"  
                        },
                }
    }
]
4

Hi @turivishal Thanks for the response,

no i don’t want count it, i want to add value of $object.TotalDaily together, but u only want to add 1 value per day, per month.

I tried to use $group this way but i get a error.

{
  _id: {
            year:{$year:"$rxInfo.time"},
            month:{$month:"$rxInfo.time"},
            day:{$dayOfMonth:"$rxInfo.time"}
        },
        "Total": 
        {
          "$sum": "$object.TotalDaily"
        }
}

but keeps telling me “can’t convert from BSON type array to Date”.

Thanks

1 Like
5

The field "$rxInfo.time" having string type date and also inside an array, it should be date type and outside the array, you need to deconstruct the rxInfo array using $unwind, and than use $group stage.

You have already did in your second reply, you can convert it using $dateFromString or $toDate Date Expression Operators, Before the $group stage inside $addFields, or inside the $group stage that you have already did.

7

Hi, I need to order all the data by months from an aggregate in a list record

something like this

January

01-01-2020

filed1,field2,field3

02-01-2020

fieild1,field2,field3

February

01-02-2020

field1,field2,field3

02-02-2020

field1,field2,field3

April

01-04-2020

field1,field2,field3

02-04-2020

field1,field2,field3

And I have no clue how to do it.

8

Hello @jack_austin, Welcome to the MongoDB community forum,

This topic is not related to the current topic, Can you please ask your question in a new post?

9

@turivishal ok no problem. I will create a new topic.