title: Count posts by date for all users
body: <p>I have <code>users</code> and <code>posts</code> table and would like to get a count of posts by a user for various date spans.</p>
  
  <pre><code>users: id, username
  1, bob
  2, sam
  
  posts: id, user_id, created (datetime)
  1, 1, 2012-12-01 00:00:00
  2, 1, 2012-12-30 00:01:00
  3, 1, 2012-12-30 01:00:00
  4, 2, 2012-12-12 00:00:00
  5, 2, 2012-12-30 01:00:00
  </code></pre>
  
  <p>I want to count the posts for the last <code>hour</code>, <code>day</code> and month. Can this be done in one query?</p>
  
  <p>Say the date the query is run is <code>2012-12-30 01:30:00</code> I would want something like the following:</p>
  
  <pre><code>User.id | hour | day | month
  1       | 1    | 2   | 3
  2       | 1    | 1   | 2
  </code></pre>
  
  <p>I have tried to something like the following but the numbers are wrong. </p>
  
  <pre><code>SELECT 
      User.id, 
      COUNT(Hour.id) as hour, 
      COUNT(Day.id) as day, 
      COUNT(Month.id) as month
  FROM user as User
  LEFT JOIN posts as Hour ON 
      (posts.user_id = user.id AND posts.created &gt;= '2012-12-30 00:30:00')
  LEFT JOIN posts as Day ON 
      (posts.user_id = user.id AND posts.created &gt;= '2012-12-29 01:30:00')
  LEFT JOIN posts as Month ON 
      (posts.user_id = user.id AND posts.created &gt;= '2012-11-30 01:30:00')
  </code></pre>
  
  <p>I end up with strange numbers like</p>
  
  <pre><code>User.id | hour | day | month
  1       | 12   | 12  | 12
  2       | -    | 2   | 2
  </code></pre>