This is Invoice Collection:
db.Invoice.insert({"InvoiceNo" : "1", "Total" : "22.50", "FoodId" : "1", "SupplierType" : "Meat"})
WriteResult({ "nInserted" : 1 })
db.Invoice.insert({"InvoiceNo" : "2", "Total" : "22.50", "FoodId" : "2", "SupplierType" : "Meat"})
WriteResult({ "nInserted" : 1 })
db.Invoice.insert({"InvoiceNo" : "3", "Total" : "12.50", "FoodId" : "3", "SupplierType" : "Veg"})
WriteResult({ "nInserted" : 1 })
db.Invoice.insert({"InvoiceNo" : "3", "Total" : "12.50", "FoodId" : "3", "SupplierType" : "Veg"})
WriteResult({ "nInserted" : 1 })
db.Invoice.insert({"InvoiceNo" : "4", "Total" : "12.50", "FoodId" : "4", "SupplierType" : "Veg"})
WriteResult({ "nInserted" : 1 })
db.Invoice.insert({"InvoiceNo" : "5", "Total" : "15.00", "FoodId" : "5", "SupplierType" : "Meat"})
WriteResult({ "nInserted" : 1 })
db.Invoice.insert({"InvoiceNo" : "6", "Total" : "19.00", "FoodId" : "6", "SupplierType" : "Meat"})
WriteResult({ "nInserted" : 1 })
db.Invoice.insert({"InvoiceNo" : "7", "Total" : "10.00", "FoodId" : "7", "SupplierType" : "Veg"})
WriteResult({ "nInserted" : 1 })
db.Invoice.insert({"InvoiceNo" : "8", "Total" : "10.00", "FoodId" : "8", "SupplierType" : "Veg"})
WriteResult({ "nInserted" : 1 })
db.Invoice.insert({"InvoiceNo" : "9", "Total" : "10.00", "FoodId" : "9", "SupplierType" : "Veg"})
WriteResult({ "nInserted" : 1 })
db.Invoice.insert({"InvoiceNo" : "10", "Total" : "25.00", "FoodId" : "10", "SupplierType" : "Meat"})
WriteResult({ "nInserted" : 1 })
And this is Food Collection:
db.Food.insert({"FoodId" : "1", "FoodType" : "Steak","BestBeforeDate" : "07/05/2018",})
WriteResult({ "nInserted" : 1 })
db.Food.insert({"FoodId" : "2", "FoodType" : "Steak","BestBeforeDate" : "07/05/2018",})
WriteResult({ "nInserted" : 1 })
db.Food.insert({"FoodId" : "3", "FoodType" : "Carrot","BestBeforeDate" : "09/05/2018",})
WriteResult({ "nInserted" : 1 })
db.Food.insert({"FoodId" : "3", "FoodType" : "Carrot","BestBeforeDate" : "17/05/2018",})
WriteResult({ "nInserted" : 1 })
db.Food.insert({"FoodId" : "4", "FoodType" : "Peas","BestBeforeDate" : "09/05/2018",})
WriteResult({ "nInserted" : 1 })
db.Food.insert({"FoodId" : "5", "FoodType" : "Ribbs","BestBeforeDate" : "05/05/2018",})
WriteResult({ "nInserted" : 1 })
db.Food.insert({"FoodId" : "6", "FoodType" : "Ribbs","BestBeforeDate" : "05/05/2018",})
WriteResult({ "nInserted" : 1 })
db.Food.insert({"FoodId" : "7", "FoodType" : "Chips","BestBeforeDate" : "30/05/2018",})
WriteResult({ "nInserted" : 1 })
db.Food.insert({"FoodId" : "8", "FoodType" : "Chips","BestBeforeDate" : "30/05/2018",})
WriteResult({ "nInserted" : 1 })
db.Food.insert({"FoodId" : "9", "FoodType" : "Chips","BestBeforeDate" : "30/05/2018",})
WriteResult({ "nInserted" : 1 })
db.Food.insert({"FoodId" : "10", "FoodType" : "Chicken","BestBeforeDate" : "30/05/2018",})
WriteResult({ "nInserted" : 1 })
I want to multiply the total by the count function for the Food Collection.
Next I want to count how many records where FoodType
is equal to "Steak". I want to multiply the result from the count by the total in the Invoice Collection.
This is the command that I thought would work:
db.Food.aggregate(
[
{ $project:
{ date: 1, item: 1,
total: { $multiply:
[
{"FoodType": "Carrot"}.count(), "Total"}
]
}
}
}
]
)
I want to count the number of steak entries in the Food Table. Then I want to multiply that result by the total corresponding price in the Invoice Table.