title: rails COUNT SELECT DISTINCT
body: <p>I'm recording the number of times users watch a series of videos.  Now I'm trying to make a graph of the number of users who watch any video each day.</p>
  
  <pre><code>UserVideoWatching.where("created_at &gt;= ? AND user_id != ?",1.month.ago, User.elephant.id).group("DATE(created_at)").reorder('created_at').count
  </code></pre>
  
  <p>produces the sql</p>
  
  <pre><code>SELECT COUNT(*) AS count_all, DATE(created_at) AS date_created_at FROM `user_video_watchings` WHERE (created_at &gt;= '2013-01-27 10:43:24' AND user_id != 7) GROUP BY DATE(created_at) ORDER BY created_at
  </code></pre>
  
  <p>which produces the correct results for all videos watched each day, but as I said I want to only show each user once.</p>
  
  <p>The sql I want is</p>
  
  <pre><code>SELECT COUNT(DISTINCT user_id) AS count_all, DATE(created_at) AS date_created FROM `user_video_watchings` WHERE (created_at &gt;= '2013-01-27 10:33:18' AND user_id != 7) GROUP BY DATE(created_at) ORDER BY created_at
  </code></pre>
  
  <p>so i thought</p>
  
  <pre><code>UserVideoWatching.where("created_at &gt;= ? AND user_id != ?",1.month.ago, User.elephant.id).group("DATE(created_at)").reorder('created_at').select('COUNT(DISTINCT user_id) AS count_all, DATE(created_at) AS date_created')
  </code></pre>
  
  <p>would do what I wanted. But this gives</p>
  
  <pre><code>[#&lt;UserVideoWatching &gt;, #&lt;UserVideoWatching &gt;]
  </code></pre>
  
  <p>rather than a hash.</p>
  
  <p>Any ideas?</p>
  
  <p>I'm using rails 3.1 and mysql</p>