title: Mongo aggregate pipeline project groups on match (equivalent to SQL select sum as group)
body: <p>I am new to Mongo DB and would really appreciate some help on the following query on the database. I basically need to select all the results where the ‘Outcome’ field is in ‘first’, ‘second’ or ‘third’ and project that grouped within countries as ‘medals’ (where medals were won).</p>
  
  <p>I then need to do the reverse in the pipeline to select where medals were not won. I guess the equivalent in SQL would be select the sun of the entries where the outcome is in ‘first’, ‘second’ or ‘third’ as ‘medals’.And select the sum of the entries not in ‘first’, ‘second’ or ‘third’ as ‘non_medals’. Then groups the results by country.</p>
  
  <p>Below is the query I have managed to come up with thus far, but cannot seem to get it right.</p>
  
  <p>pipeline_4 = [    </p>
  
  <pre><code>{'$match': {'Outcome': {'$in': ['first','second', 'third'] } ,'Country': {'$exists': True}}},
  
  {'$group': {'_id': {'outcome': '$Outcome', 'country': '$Country'},
                      'medals': {'$sum': 1}}},
  
  {'$project': {
      'outcome': 1, 'country', 1, 'medals': 1
  }},
  
  {'$match': {'Outcome': {'$nin': ['first','second', 'third'] } ,'Country': {'$exists': True}}},
  
  {'$group': {'_id': {'outcome': '$Outcome', 'country': '$Country'},
                      'non_medals': {'$sum': 1}}},
  
  {'$project': {
      'outcome': 1, 'country', 1, 'non_medals': 1
  }}
  </code></pre>
  
  <p>]</p>
  
  <p>Could anyone please advise on this? at the moment I only get the medal groups returned and they are not grouped. If you need more info please ask, as I say I am new to Mongo and may be approaching it in too much of a standard SQL way.</p>
  
  <p>Thanks,</p>