CODEWITHSUNDEEP
javascriptnode.jsmongodbmongoosemongodb-query
user944513
user944513

Reputation: 12729

how to calculate sum in mongodb

Hi I have this type of collection.

[{
    "_id" : ObjectId("5d7e56cc61f05d22ff7ee318"),
    "Emp No" : "0088P2C",
    "Emp Name" : "MANISHA JHA",
    "Card No" : "2076",
    "Department" : "THBS",
    "Att Date" : "03-Jun-2019",
    "In Time" : "03-Jun-2019 10:53",
    "Out Time" : "03-Jun-2019 18:13",
    "Status" : "P       ",
    "Late By " : "01:53",
    "Early By " : "00:00",
    "Total Hour" : "07:20",
    "OT Hour" : "00:00",
    "Location" : "Consolidated",
    "id" : "1c4bdbcd0f57c17a7160697266e9b371"
},
{
    "_id" : ObjectId("5d7e56cc61f05d22ff7ee319"),
    "Emp No" : "0088P2C",
    "Emp Name" : "MANISHA JHA",
    "Card No" : "2076",
    "Department" : "THBS",
    "Att Date" : "04-Jun-2019",
    "In Time" : "04-Jun-2019 09:50",
    "Out Time" : "04-Jun-2019 15:33",
    "Status" : "P       ",
    "Late By " : "00:50",
    "Early By " : "02:27",
    "Total Hour" : "05:43",
    "OT Hour" : "00:00",
    "Location" : "Consolidated",
    "id" : "a54e78844d0c27bd15607feb50aa272b"
}]

I want to calculate total hours.i query like that aggregate employeesand get it's total hour

await Attendance.aggregate(
            [
                {$match: {Department: "THBS"}},
                {
                    $group: {
                        _id: '$Emp No',
                        totalTime: {$sum: "$Total Hour"},
                        data: {$addToSet: {attendance: "$Att Date", Status: "$Status", id: "$id"}}
                    }
                },
                {$project: {'Emp No': '$_id', _id: 0, data: 1, totalTime: 1}}
            ])

currently total hour coming 0 from this query.expected should be 12.63

Upvotes: 2

Views: 193

Answers (1)

mickl
mickl

Reputation: 49945

Try to convert Total Hour stored as string into minutes using $toInt, $substr and $multiply. Then you can $sum that value and convert back into number of hours using $divide:

db.collection.aggregate([
    {$match: {Department: "THBS"}},
    {
        $addFields: {
        TotalMinutes: {
            $add: [ 
                { $toInt: { $substr: [ "$Total Hour", 3, 2 ] } },
                { $multiply: [ { $toInt: { $substr: [ "$Total Hour", 0, 2 ] } }, 60 ] }
            ]
        }
        }
    },
    {
        $group: {
            _id: '$Emp No',
            totalTime: {$sum: "$TotalMinutes"},
            data: {$addToSet: {attendance: "$Att Date", Status: "$Status", id: "$id"}}
        }
    },
    {
        $project: {
            'Emp No': '$_id', 
            _id: 0, 
            data: 1, 
            totalTime: { $add: [ 
                { $toInt: { $divide: [ "$totalTime", 60 ] } },
                { $divide: [ { $mod: [ "$totalTime", 60 ] }, 100 ] }
                ]
            }
        }
    }
])

Mongo Playground

EDIT: 12.63 is equal to 13 hours and 3 minutes in this case

Upvotes: 1

Related Questions