title: Writing a Mathematical Formula in SQL?
body: <p>I have these tables: <code>users</code>, <code>comments</code>, <code>ratings</code>, and <code>items</code></p>
  
  <p>I would like to know if it is possible to write SQL query that basically does this:</p>
  
  <p>user_id is in each table.  I'd like a SQL query to count each occurrence in each table (except <code>users</code> of course). BUT, I want some tables to carry more weight than the others.  Then I want to tally up a "score".</p>
  
  <p>Here is an example:</p>
  
  <p>user_id 5 occurs...
  2 times in items;
  5 times in comments;
  11 times in ratings.</p>
  
  <p>I want a formula/point system that totals something like this:</p>
  
  <p>items 2 x 5 = 10;
  comments 5 x 1 = 5;
  ratings 11 x .5 = 5.5</p>
  
  <p>TOTAL 21.5</p>
  
  <p>This is what I have so far.....</p>
  
  <pre><code>SELECT u.users
       COUNT(*) r.user_id
       COUNT(*) c.user_id
       COUNT(*) i.user_id
  FROM users as u
  JOIN COMMENTS as c
       ON u.user_id = c_user_id
  JOIN RATINGS as r
       ON r.user_id = u.user_id
  JOIN ITEMS as i
       i.user_id = u.user_id
  WHERE
      ????
  GROUP BY u.user_id
  ORDER by total DESC
  </code></pre>
  
  <p>I am not sure how to do the mathematical formula portion (if possible).  Or how to tally up a total. </p>
  
  <p><strong>Final Code based on John Woo's Answer!</strong></p>
  
  <pre><code>$sql = mysql_query("
          SELECT  u.username,
      (a.totalCount * 5) +
      (b.totalCount) +
      (c.totalCount * .2) totalScore
      FROM users u 
      LEFT JOIN
          (
              SELECT user_id, COUNT(user_id) totalCount
              FROM items 
              GROUP BY user_id
          ) a ON a.user_id= u.user_id
      LEFT JOIN
          (
              SELECT user_id, COUNT(user_id) totalCount
              FROM comments
              GROUP BY user_id
          ) b ON b.user_id= u.user_id
      LEFT JOIN
          (
              SELECT user_id, COUNT(user_id) totalCount
              FROM ratings
              GROUP BY user_id
          ) c ON c.user_id = u.user_id
      ORDER BY totalScore DESC LIMIT 10;");
  </code></pre>