title: Count occurrences of a grouped count in mysql group by statement
body: <p>I'm trying to count the number of distinct results in count statement to find the most prolific user in my database. Each user interaction has been entered as user name and interaction number. </p>
  
  <pre><code>TABLE
  | name | interaction | filter data |
  |   1  |      A      |             |
  |   1  |      A      |  some stuff |
  |   1  |      B      |  to filter  |
  |   1  |      B      |  for the    | 
  |   1  |      B      |  queries    |
  |   2  |      C      |             | etc...
  </code></pre>
  
  <p>I want to: a) find the most number of interactions on a given user:</p>
  
  <pre><code>RESULT A
  | name | interaction | int_count |
  |   1  |      A      |     2     |
  |   1  |      B      |     3     |
  |   2  |      C      |     1     |
  </code></pre>
  
  <p>then b) find the most prolific user:</p>
  
  <pre><code>RESULT B
  | name | interaction | int_count | user_occur |
  |   1  |      A      |     2     |     2      |
  |   1  |      B      |     3     |     2      |
  |   2  |      C      |     1     |     1      |
  </code></pre>
  
  <p>where user_occur is the number of times that 'name'  shows up in query A</p>
  
  <p>I have query A no problem, and I'm trying to use a JOIN statement to get the user_count for query B:</p>
  
  <pre><code>SELECT origin.name, origin.interaction, 
  counter.int_count, count(counter.name) AS user_occur
  FROM table AS origin
  LEFT JOIN(
      SELECT name, COUNT(interaction) AS int_count
      FROM table
      WHERE [filter-conditions]
      GROUP BY interaction) counter
  ON origin.name = counter.name
  WHERE [same-filter-conditions]
  GROUP BY origin.interaction
  ORDER BY counter.int_count DESC
  </code></pre>
  
  <p>Something is going wrong with this though, because I'm just getting the interaction number in every user_occur row, when I want a list of their interactions, and then a count at the end of each row of their total number of interactions (user_occur should be the same number for a given name). I tried COUNT(DISTINCT counter.name) but that just gave me 1 for user_occur (of course, in retrospect).</p>
  
  <p>I can't figure out how to get this last step - I'm not super strong with MySQL, so any insight would be appreciated. I'd doubly appreciate a little explanation on why it's acting in this way, and how to think about structuring queries for next time.</p>