title: SUM of Multiple COUNTs on Different Tables
body: <p>This topic appears to be a popular one and definitely saturated in terms of the number of related posts, however, I've been working on this for 3 days and I cannot get this figured out.  </p>
  
  <p>I've been scouring this site and many others with potential solutions to this and some are executing, but I am not getting the expected results.</p>
  
  <p>Here's what I'm trying to do...</p>
  
  <ol>
  <li>SELECT and COUNT the number of reviews a user has submitted in the reviews table.</li>
  <li>SELECT and COUNT the number of up-votes a user has in the reviewVotes table.</li>
  <li>GROUP BY username (which is a key in both tables - usernames are unique, but exist in multiple rows).</li>
  <li>Order the result set by the SUM of those COUNTs DESC. (This is something I keep trying, but can't get to even execute, so I am ordering by userReviewNum DESC right now.)</li>
  <li>LIMIT the result set to the first 10.</li>
  </ol>
  
  <p>The result set should give me the top 10 reviewers which is calculated by the number of reviews plus (+) the number of up-votes.</p>
  
  <p>Here is my latest attempt which executes, but appears to be multiplying userReviewNum * reviewVotesNum and I need it to add them (but I have been extremely unsuccessful at any attempt to include the SUM command - so bad in fact that I am embarrassed to even show my attempts).</p>
  
  <pre><code>    SELECT
      reviews.username,
      count(reviews.username) userReviewNum,
      count(reviewVotes.username) reviewVotesNum
      FROM reviews 
      LEFT JOIN reviewVotes ON reviews.username = reviewVotes.username 
      GROUP by reviews.username
      ORDER BY userReviewNum DESC 
      LIMIT 0, 10
  </code></pre>
  
  <p>I've tried using a JOIN and a UNION and I can't seem to get either of them to work.</p>
  
  <p>Any help anyone can provide is greatly appreciated!</p>
  
  <p><strong>UPDATE:</strong></p>
  
  <p>Here is the structure and some sample data.</p>
  
  <p>Reviews Table (there are other fields, but these are the important ones):</p>
  
  <p><strong>| username | comment | rating | productID |</strong></p>
  
  <p>| foo | this is awesome! | 5 | xxxx |</p>
  
  <p>| bar | i don't like this | 1 | xxxx |</p>
  
  <p>| foo2 | it's ok | 3 | xxxx |</p>
  
  <p>| foo | bleh - nasty | 1 | xxxx |</p>
  
  <p>reviewVotes Table (again, more fields than this, but these are the important ones):</p>
  
  <p><strong>| username | voterUsername | productID |</strong></p>
  
  <p>| foo | foo2 | xxxx |</p>
  
  <p>| foo2 | foo | xxxx | (the simple idea here is one user is up-voting another user's post)</p>
  
  <p>So I need to count the number of reviews a user has in the Reviews table, then count the number of upvotes a user has in the reviewVotes table, and then order by the sum of those two numbers.</p>
  
  <p><strong>Additional UPDATE:</strong>
  In the example above, here are the expected results:</p>
  
  <p><strong>Username  |  # Reviews</strong></p>
  
  <p>foo  |  2</p>
  
  <p>bar  |  1</p>
  
  <p>foo2  |  1</p>
  
  <p><strong>Username  |  # Up-Votes</strong></p>
  
  <p>foo  |  1</p>
  
  <p>foo2  |  1</p>
  
  <p><strong>Username  |  Total Sum</strong></p>
  
  <p>foo  |  3</p>
  
  <p>bar  |  1</p>
  
  <p>foo2  |  2</p>