Use Mongo aggregation to calculate potential cost

user_5649739 0
Viewed 104

For the below collection, I want to calculate the potential cost of cooper_inc manufacturer in a particular shop. In our example's case, the shop name is 4AM Mart. There are three manufacturer suppliers to 4AM Mart = {Cooper_inc, jason_inc, yeoman_inc}.

The formula for potential cost = (annual cost of items sold by cooper_inc to 4AM Mart) + (annual cost of other manufacturers selling **similar items** in 4AM Mart).

Cooper Inc supplies bread and cheese to 4AM Mart. We have to calculate by summing up the annual cost of bread and cheese supplied by Cooper Inc with annual cost of bread and cheese items supplied by other manufacturers to 4AM Mart.

Example calculation is pot_cost = (45+60) + ((56+54) + (34+80)) = $329

Please find below an example collection.

[
  {
    _id: "xxxsdsdsds",
    category: "food",
    sub_category: "bread",
    manufacturer: "cooper_inc",
    brand_name: "cooper_breads",
    cost: 45,
    shop: "4AM_Mart"
  },
  {
    _id: "ddsfdfdggf",
    category: "food",
    sub_category: "cheese",
    manufacturer: "cooper_inc",
    brand_name: "cooper_cheese",
    cost: 60,
    shop: "4AM_Mart"
  },
  {
    _id: "dsdsfdfdffdf",
    category: "food",
    sub_category: "bread",
    manufacturer: "jason_inc",
    brand_name: "jason_breads",
    cost: 56,
    shop: "4AM_Mart"
  },
  {
    _id: "wdsfdfdfdgg",
    category: "food",
    sub_category: "cheese",
    manufacturer: "jason_inc",
    brand_name: "jason_cheese",
    cost: 54,
    shop: "4AM_Mart"
  },
  {
    _id: "dfddgfgfgf",
    category: "food",
    sub_category: "bread",
    manufacturer: "yeoman_inc",
    brand_name: "yeoman_breads",
    cost: 34,
    shop: "4AM_Mart"
  },
  {
    _id: "dfdsflkdsfjdsfjd",
    category: "food",
    sub_category: "cheese",
    manufacturer: "yeoman_inc",
    brand_name: "yeoman_cheese",
    cost: 80,
    shop: "4AM_Mart"
  },
  {
    _id: dfdfnjdsnfdnfnfds,
    category: "beverage",
    sub_category: "energy_drink",
    manufacturer: "red_bull",
    brand_name: "red_bull_energy_drink",
    cost: 50,
    shop: "4AM_Mart"
  },
  {
    _id: "xxxsdsdsdsss",
    category: "food",
    sub_category: "bread",
    manufacturer: "cooper_inc",
    brand_name: "cooper_breads",
    cost: 45,
    shop: "downey_mart"
  },
  {
    _id: "ddsfdfdggfsds",
    category: "food",
    sub_category: "cheese",
    manufacturer: "cooper_inc",
    brand_name: "cooper_cheese",
    cost: 60,
    shop: "downey_mart"
  },
  {
    _id: "dsdsfdfdffdfssds",
    category: "food",
    sub_category: "bread",
    manufacturer: "jason_inc",
    brand_name: "jason_breads",
    cost: 56,
    shop: "downey_mart"
  },
  {
    _id: "wdsfdfdfdggdsdsd",
    category: "food",
    sub_category: "cheese",
    manufacturer: "jason_inc",
    brand_name: "jason_breads",
    cost: 54,
    shop: "downey_mart"
  },
  {
    _id: "dfddgfgfgfsdsfsds",
    category: "food",
    sub_category: "bread",
    manufacturer: "yeoman_inc",
    brand_name: "yeoman_breads",
    cost: 34,
    shop: "downey_mart"
  },
  {
    _id: "dfdsflkdsfjdsfjddsfdgfg",
    category: "food",
    sub_category: "cheese",
    manufacturer: "yeoman_inc",
    brand_name: "yeoman_cheese",
    cost: 80,
    shop: "downey_mart"
  }
]

I have written the below mongo query.

db.collection.aggregate([
  {
    "$match": {
      manufacturer: {
        $in: [
          "cooper_inc"
        ]
      },
      shop: {
       $in: [
          "4AM_Mart"
        ]
      }
    }
  },
  {
    "$group": {
      _id: {
        "Shop Name": "$shop",
        "Category": "$category",
        "Sub-Category": "$sub_category"
      },
      "annual_cost": {
        $sum: "$cost"
      },
      count: {
        $sum: 1
      }
    }
  },
  {
    $project: {
      "Annual Purchase Cost": "$annual_cost"
    }
  }
])

The above query would result in the following response.

[
  {
    "Annual Purchase Cost": 45,
    "_id": {
      "Category": "food",
      "Shop Name": "4AM_Mart",
      "Sub-Category": "bread"
    }
  },
  {
    "Annual Purchase Cost": 60,
    "_id": {
      "Category": "food",
      "Shop Name": "4AM_Mart",
      "Sub-Category": "cheese"
    }
  }
]

It is giving only the annual cost of items sold by cooper_inc to 4AM Mart. It is not giving the annual cost of similar items sold by other manufacturers to 4AM Mart and also the summation as described above.

Any help would be of great help.

aggregation-framework mongodb-query mongodb
2 Answers

In a single aggregation you can find the result like this:

db.collection.aggregate([
  {
    "$match": {
      "shop": "4AM_Mart",
      "category": "food",
      "sub_category": { $in: ["bread", "cheese" ] }
    }
  },
  {
    "$group": {
      _id: "$shop",
      totalAmount: {
        $sum: {
          "$cond": [
            {
              "$ne": [
                "$manufacturer",
                "cooper_inc"
              ]
            },
            "$cost",
            0
          ]
        }
      },
      singleAmount: {
        $sum: {
          $cond: [
            {
              "$eq": [
                "$manufacturer",
                "cooper_inc"
              ]
            },
            "$cost",
            0
          ]
        }
      }
    }
  },
  {
    "$project": {
      result: {
        "$sum": [
          "$totalAmount",
          "$singleAmount"
        ]
      }
    }
  }
])

Playground

user_137720900

I am running this aggregation on your collection

db.cooper.aggregate([
  { $match: { shop: "4AM_Mart", category:"food", sub_category:{$in:["bread","cheese"] } }, 
  { $group: 
            { _id: {manufacturer:"$manufacturer"}, 
              totalCost: { $sum: "$cost" } 
            } 
   }
])

The result is,

[
  { _id: { manufacturer: 'yeoman_inc' }, totalCost: 114 },
  { _id: { manufacturer: 'cooper_inc' }, totalCost: 105 },
  { _id: { manufacturer: 'jason_inc' }, totalCost: 110 }
]

And to get the total,

db.cooper.aggregate([
  { $match: { shop: "4AM_Mart", category:"food", sub_category:{$in:["bread","cheese"] } }, 
  { $group: 
            { _id: 0, 
              totalCost: { $sum: "$cost" } 
            } 
   }
])

the result is,

[ { _id: 0, totalCost: 329 } ]

Playground

user_96482520
Related