title: MongoDB aggregate with conditional sums
body: <p>I have to do a special mongoDB query.</p>
  
  <p>I have a collection with documents like these:</p>
  
  <pre><code>{
      "_id" : ObjectId("53c7fd86d624b06abc76e8f6"),
      "works" : [ 
          {
              "code" : "A001",
              "name" : "Cambiar bombilla",
              "orderId" : "53c7fd86d624b06abc76e8f6",
              "price" : 1400,
              "ID" : 1,
              "lazyLoaded" : true,
              "status" : 0,
              "Date" : ISODate("2014-07-21T10:31:55.063Z"),
              "TechnicianId" : "538efd918163b19307c59e8e",
              "_id" : ObjectId("53ccec1bf2bf4d5952b2f205")
          }, 
          {
              "code" : "A005",
              "name" : "Cambiar bombilla 5",
              "price" : 1050,
              "type" : "Bombillas",
              "TechnicianId" : "5383577a994be8b9a9e3f01e",
              "_id" : ObjectId("53ccfdbdf2bf4d5952b2f206")
          }, 
          {
              "code" : "A004",
              "name" : "Cambiar bombilla 4",
              "price" : 1010,
              "type" : "Bombillas",
              "TechnicianId" : "5383577a994be8b9a9e3f01e",
              "date" : "2014-07-21T11:50:52.702Z",
              "orderId" : "53c7fd86d624b06abc76e8f6",
              "_id" : ObjectId("53ccfe9c109c100000ad688a")
          }, 
          {
              "code" : "A002",
              "name" : "Cambiar bombilla 2",
              "price" : 1030,
              "type" : "Bombillas",
              "TechnicianId" : "5383577a994be8b9a9e3f01e",
              "date" : "2014-07-21T11:57:37.065Z",
              "orderId" : "53c7fd86d624b06abc76e8f6",
              "_id" : ObjectId("53cd0036109c100000ad688b")
          }, 
          {
              "code" : "A003",
              "name" : "Cambiar bombilla 3",
              "price" : 1050,
              "type" : "Bombillas",
              "TechnicianId" : "5383577a994be8b9a9e3f01e",
              "date" : "2014-07-21T11:59:35.586Z",
              "orderId" : "53c7fd86d624b06abc76e8f6",
              "_id" : ObjectId("53cd00a7109c100000ad688c")
          }
      ],
      "Items" : [ 
          {
              "_id" : "534ba71f394835a7e51dd938",
              "total":50
              "qty" : 2
          }, 
          {
              "_id" : "534664b081362062015d1b77",
              "qty" : 2,
              "total":30
          }
      ]}
  </code></pre>
  
  <p>Now, I want to do a query to get the works with TechnicianId=5383577a994be8b9a9e3f01e , the sum of the "price" for these works deducting the sum of items.total, the quantity of the total works for these documents deducting the sum of items.total.</p>
  
  <p>For this example I want something like this:</p>
  
  <pre><code>{
      "result" : [ 
          {
              "_id" : ObjectId("53c7fd86d624b06abc76e8f6"),
              "works.totalsumfortech" : 1050+1010+1030+1050 - (50+30),//Sum of works of this technicianId deducting the items.total qty
              "works.total":1400+1050+1010+1030+1050-(50+30)//Summ of all works of document deducting the items.total qty
          }
      ],
      "ok" : 1
  }
  </code></pre>
  
  <p>This is my current query but I don't get the expected result..</p>
  
  <pre><code>db.orders.aggregate([
          { "$match": {
              "$and": [
                  {"OrderState": {$in:['Review','Archived']}},
                  {'works.TechnicianId':'5383577a994be8b9a9e3f01e'}
              ]
          }},
          {$group: {
           _id: "$_id",
           'total': {$subtract:[{$sum: { $cond: [ { $eq: [ "$works.TechnicianId", "5383577a994be8b9a9e3f01e" ] } , 2, 1 ]},{$sum: '$Items.total'}]
           'total': {$subtract:[{$sum: '$works.price'},{$sum: '$Items.total'}]
           }
           }]);
  </code></pre>