CODEWITHSUNDEEP
mongodb
Kim Stacks
Kim Stacks

Reputation: 10812

How to average the summed up values in mongodb?

Using MongoDB 2.4.8,

I have the following records

{
    "category" : "TOYS",
    "price" : 12,
    "status" : "online",
    "_id" : "35043"
}
{
    "category" : "TOYS",
    "price" : 13,
    "status" : "offline",
    "_id" : "35044"
}
{
    "category" : "TOYS",
    "price" : 22,
    "status" : "online",
    "_id" : "35045"
}
{
    "category" : "BOOKS",
    "price" : 13,
    "status" : "offline",
    "_id" : "35046"
}
{
    "category" : "BOOKS",
    "price" : 17,
    "status" : "online",
    "_id" : "35047"
}

I want to find the average price of each category whose status is online and total price is more than 50.

I am not sure how to construct this query.

So far, I can construct the query where I summed up and find out the total price for each category whose status is online.

db.products.aggregate([
    {"$match":
        {           
            {status:"online"}
        }
    },
    {"$group" :
        {
            "_id": "$category",
            "total_price": {$sum:"$price"},
        }
    }
])

I am not sure how to add more stages to this query to get the averages I am looking for.

Upvotes: 1

Views: 3045

Answers (2)

Stennie
Stennie

Reputation: 65323

You can calculate the average product price per category in the $group step with the total, and then add an extra $match stage to limit the results to products with total of more than 50:

db.products.aggregate(

    // Find matching products (can take advantage of index)
    { $match: {
        status: "online"
    }},

    // Calculate total and average
    { $group: {
        "_id": "$category",
        "total_price": { $sum:"$price" },
        "avg_price": { $avg:"$price"} 
    }},

    // Limit results to price > 50
    { $match: {
        "total_price" : { $gt: 50 }
    }}
)

Note that with your example data, there would be no matching results for $gt:50 (you could instead try with $gt:30 to get the "TOYS" category as a match with total price of 34).

Averaging total prices for matching categories

If you want to get the average price for the total prices of the categories matching the limit, you can add an extra $group step at the end:

// Calculate the average total price
{ $group: {
    "_id": null,
    "total_average_price": { $avg:"$total_price"} 
}}

Note that this extra grouping is going to reduce everything down to one number (the total_average_price) which may or may not be what you expect. You might want to save the intermediate results before running the aggregation with the last group, or just calculate the average in your application code if there aren't a lot of numbers to sum up.

Upvotes: 2

Simplefish
Simplefish

Reputation: 1130

You can just add more stages to your aggregation pipeline. For example:

db.items.aggregate([
    {$match:
        {           
            status:"online"
        }
    },
    {$group :
        {
            _id: "$category",
            total_price: {$sum:"$price"},
        }
    },
    {$match:
        {           
            total_price:{$gt:50}
        }
    },
    {$group :
        {
            _id: "1",
            avg_price: {$avg:"$total_price"},
        }
    },
]);

EDITTED based on clarifications

Upvotes: 3

Related Questions