How to calculate total revenue using MongoDB Aggregate

Question

I want to calculate the total revenue using this equation (units_sold * retail_price ) - (units_sold * price ). I have the individual numbers but unsure how to combine and subtract values to get desired outcome.

db.sales.aggregate([{
    "$group" : { 
        "_id" : null, 
        "Money Spent" : { 
            "$sum" : { 
                "$multiply" : ["$units_sold", "$price"]
            }
        }
    }
}]);



db.sales.aggregate([{
    "$group" : { 
        "_id" : null, 
        "Total Sales" : { 
            "$sum" : { 
                "$multiply" : ["$units_sold", "$retail_price"]
            }
        }
    }
}]);

Answer 1

Let us assume there is sales collection with documents with fields { units_sold, retail_price, price }:

> db.sales.find()
{ "_id" : ObjectId("6249e22bd201d640bf506f62"), "units_sold" : 5, "price" : 4, "retail_price" : 3 }
{ "_id" : ObjectId("6249e232d201d640bf506f63"), "units_sold" : 2, "price" : 3, "retail_price" : 2 }

Then you can just combine the calculations you already implemented correctly, except both in the same $group pipeline stage:

db.sales.aggregate([{
  "$group": {
    "_id" : null,
    "total_sales": {
      "$sum": {
        "$multiply": ["$units_sold", "$retail_price"]
      }
    },
    "money_spent" : {
      "$sum" : {
        "$multiply" : ["$units_sold", "$price"]
      }
    }
  }
}]);

Output is a single document with { money_spent, total_sales }:

> db.sales.aggregate([{
...   "$group": {
...     "_id" : null,
...     "total_sales": {
...       "$sum": {
...         "$multiply": ["$units_sold", "$retail_price"]
...       }
...     },
...     "money_spent" : {
...       "$sum" : {
...         "$multiply" : ["$units_sold", "$price"]
...       }
...     }
...   }
... }]);
{ "_id" : null, "total_sales" : 19, "money_spent" : 26 }

The pipeline was calculated in the server; you may just subtract the two values in the client. If you really want to output the document with the resulting revenue, you can add another $project to this pipeline:

> db.sales.aggregate([
...   {
...     "$group": {
...       "_id" : null,
...       "total_sales": {
...         "$sum": {
...           "$multiply": ["$units_sold", "$retail_price"]
...         }
...       },
...       "money_spent" : {
...         "$sum" : {
...           "$multiply" : ["$units_sold", "$price"]
...         }
...       }
...     },
...   }, {
...   "$project": {
...     "revenue": { 
...       "$subtract": ["$money_spent", "$total_sales"]
...       }
...     }
...   }
... ]);
{ "_id" : null, "revenue" : 7 }

In this document, revenue: 7 is your result.