title: Wordpress - Get number of posts AND comments by day
body: <p>I need to weight how much activity has been in a Wordpress blog. Say, some day there are 3 posts and 10 comments, the points awarded for a post is 10 and just 1 for a comment, then said day had 40 points in total. However, there might be some days with no post activity or with no comment activity.</p>
  
  <p>My first idea was a simple <code>LEFT JOIN</code> from the posts to the comments table. However, this will exclude days without posts. I'm no MySQL guru, but I've been researching and it seems that the best way to solve this is a with <code>FULL OUTER JOIN</code> (<a href="http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html" rel="nofollow">explained by Jeff Atwood</a>), but MySQL doesn't suppor this!</p>
  
  <p>Then, there actually is  <a href="http://www.xaprb.com/blog/2006/05/26/how-to-write-full-outer-join-in-mysql/" rel="nofollow">a workaround</a>, but it's not working for me. It seems that the <code>RIGHT OUTER JOIN</code> is not returning what I need.<br>
  Here's the <code>LEFT</code> one, it works pretty good.</p>
  
  <pre><code>SELECT
      DISTINCT DATE(post_date) AS day,
      COUNT(ID) AS post_total,
      COUNT(comment_ID) as comment_total,
      (COUNT(ID)*10 + COUNT(comment_ID)*1) AS total
  FROM wp_posts
      LEFT OUTER JOIN wp_comments ON
          DATE(post_date) = DATE(comment_date)
  GROUP BY day ORDER BY total DESC
  </code></pre>
  
  <p>But something's wrong with the <code>RIGHT</code> one.</p>
  
  <pre><code>SELECT
      DISTINCT DATE(post_date) AS day,
      COUNT(ID) AS post_total,
      COUNT(comment_ID) as comment_total,
      (COUNT(ID)*10 + COUNT(comment_ID)*1) AS total
  FROM wp_posts
      RIGHT OUTER JOIN wp_comments ON
          DATE(post_date) = DATE(comment_date)
  GROUP BY day ORDER BY total DESC
  </code></pre>
  
  <p>Hence, the <code>UNION</code> workaround is useless.</p>
  
  <p>What am I doing wrong? Is there a simpler way to do this?</p>
  
  <p>Thanks.</p>
  
  <p>Note: You'll have to add some posts and comments in different dates.</p>