title: MySQL - Count number of rows in each group
body: <p>I have a SQL table <code>user_game</code> which contains the games that a user owns:</p>
  
  <pre><code>| id | user_id | game_id |
  |----|---------|---------|
  | 83 | 1       | 1       |
  | 84 | 1       | 2       |
  | 85 | 1       | 3       |
  | 86 | 2       | 2       |
  | 87 | 2       | 3       |
  | 88 | 2       | 4       |
  | 89 | 3       | 2       |
  </code></pre>
  
  <p><em>I am trying to count the number of users which have 1 game, 2 games, 3 games.. etc.</em></p>
  
  <p>User 1 has 3 games, User 2 has 3 games, and User 3 has 1 game. Therefore <strong>these are the results I want to achieve</strong>:</p>
  
  <pre><code>| no_of_games | COUNT(no_of_games) |
  |-------------|--------------------|
  | 1           | 1                  |
  | 2           | 0                  |
  | 3           | 2                  |
  </code></pre>
  
  <p><code>COUNT(no_of_games)</code> is the number of users that have that number of games.</p>
  
  <hr>
  
  <p>I can individually get the number of users for each <code>no_of_games</code> with this query:</p>
  
  <pre><code>-- Select no. of users with 1 game
  SELECT no_of_games, COUNT(no_of_games)
  FROM
  (
    -- Select no. of games each user has
    SELECT user_id, COUNT(1) as no_of_games
    FROM user_game
    GROUP BY user_id
  ) as A
  WHERE no_of_games = 1;
  </code></pre>
  
  <p>which gives the results:</p>
  
  <pre><code>| no_of_games | COUNT(no_of_games) |
  |-------------|--------------------|
  | 1           | 1                  |
  </code></pre>
  
  <p>However I have to change the <code>no_of_games = 1</code> to 2, 3, 4... manually and <code>UNION</code> them with this solution and I can't do it for ~60 cases.</p>
  
  <p>Is there a simpler way to achieve this?</p>