title: php join and count then group and order by
body: <p>I have two tables;</p>
  
  <p><strong>Users</strong> </p>
  
  <pre><code>id | username   | club 
  -----------------------
  1  | James5     | 2 
  2  | 007        | 1 
  3  | xmen       | 2 
  4  | terminator | 2 
  </code></pre>
  
  <p><strong>suggestedusers</strong> </p>
  
  <pre><code> id | username | club 
  ----------------------
   1  | mark     |  2 
   2  | bon      |  1 
   3  | hero     |  2 
   4  | scorpio  |  2 
   5  | lame     |  5 
  </code></pre>
  
  <p>How do I join these tables to get the total of the clubs? e.g an answer like</p>
  
  <pre><code> club   |  clubCount 
   -------------------
    2     |  6
    1     |  2 
    5     |  1 
  </code></pre>
  
  <p>I was thinking of the following query;</p>
  
  <pre><code>SELECT User.club, COUNT(User.club) + COUNT(suggestedusers.club) AS clubCount FROM User, suggestedusers 
  GROUP BY User.club 
  ORDER BY clubCount DESC 
  </code></pre>
  
  <p>But the above script is not working.</p>