CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
momo
momo

Reputation: 31

sorting a mongodb by week and then suming data and avreging it

I am trying to sort a mongodb collection by week and then summing data and averaging it.

All my documents are identical:

{
    "flight_date": {
        "$date": "2015-01-01T00:00:00.000Z"
    },
    "flight_number": 20,
    "origin_city": "San Francisco, CA",
    "destination_city": "New York, NY",
    "delay_in_minutes": 6,
    "cancelled": 0
}

so far my best attempt looks like this

db.flightStats.aggregate([
    {
        $group: { _id: { $week: "$flight_date" } },
        $group: {
            _id: { delay_in_minutes: "$delay_in_minutes" },
            total_delay: { $sum: 1 },
            avgDelay: { $avg: "$delay_in_minutes" }
        }
    },
    { $sort: { avgDelay: -1 } }

]).pretty()

Upvotes: 1

Views: 447

Answers (1)

Dheemanth Bhat
Dheemanth Bhat

Reputation: 4452

So the query is grouping the data by week number and then calculating the sum and average of delay_in_minutes field and then sorting it in descending order of avgDelay.

Try this:

db.flightStats.aggregate([
    {
        $group: {
            _id: { $week: "$flight_date" },
            total_delay: { $sum: "$delay_in_minutes" },
            avgDelay: { $avg: "$delay_in_minutes" }
        }
    },
    { $sort: { avgDelay: -1 } }
]);

Update based on comment:

db.flightStats.aggregate([
    {
        $group: {
            _id: { $week: "$flight_date" },
            total_delay: { $sum: "$delay_in_minutes" },
            avgDelay: { $avg: "$delay_in_minutes" }
        }
    },
    {
        $match: {
            _id: 5 // Week number 5 for example
        }
    }
]);

Upvotes: 1

Related Questions