title: MongoDB query sort count
body: <p>Hi I have 2 Game records:</p>
  
  <p><strong>game 1</strong> with a list of players p1(<strong>alice</strong>), p2(<strong>bob</strong>), p3(<strong>matt</strong>), p4(<strong>tom</strong>)</p>
  
  <p><strong>game 2</strong> with a list of players p1(<strong>alice</strong>), p2(<strong>bob</strong>), p3(<strong>jack</strong>), p4(<strong>tom</strong>)</p>
  
  <p>I'd like to query my MongoDB collection games to sort the most recurrent people that appear in the same game as <strong>BOB</strong>. A good result in this case would be: </p>
  
  <pre><code>Alice: appear 2 times
  Tom:  appear 2 time
  Matt: appear 1 time
  Jack: appear 1 time
  </code></pre>
  
  <p>In SQL I did: </p>
  
  <pre><code>             SELECT idplayer, COUNT(idplayer) AS countplayer 
               FROM (SELECT b.idgame, b.idplayer 
               FROM associations a, associations b 
               WHERE a.idplayer="BOB" 
               AND b.idgame=a.idgame 
               AND b.idplayer &lt;&gt; "BOB"
               ORDER BY b.idgame DESC LIMIT 1000) as c
               GROUP BY idplayer 
               ORDER BY countplayer DESC LIMIT 5;
  </code></pre>
  
  <p>With mongo I am trying something like: </p>
  
  <pre><code>gamesCollection.find("{playerid : #}","BOB").sort(//counter).limit(5)...
  </code></pre>
  
  <p>Can someone help me to fix this query for MongoDB?
  Thank you</p>
  
  <p><strong>EDIT:</strong></p>
  
  <p>I think I am getting closer what I want, but still wrong query:</p>
  
  <pre><code>&gt; db.games.aggregate("[{ $match: {playerid : 'bob'} }, {$group : { _id:null, cou
  nt: { $sum: 1}} } ]")
  </code></pre>
  
  <p>please suggest a solution. thanks</p>
  
  <p><strong>EDIT 2</strong></p>
  
  <p>An example of a game document :</p>
  
  <pre><code>{ "_id" : ObjectId("514d9afb6e058b8a806bdbc0"), "gameid" : 1, "numofplayers" : 3,
   "playersList" : [{"playerid" : "matt" },{"playerid" : "bob"},{"playerid" : "alice"} ] }
  </code></pre>