rails COUNT SELECT DISTINCT

Question

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.

UserVideoWatching.where("created_at >= ? AND user_id != ?",1.month.ago, User.elephant.id).group("DATE(created_at)").reorder('created_at').count

produces the sql

SELECT COUNT(*) AS count_all, DATE(created_at) AS date_created_at FROM `user_video_watchings` WHERE (created_at >= '2013-01-27 10:43:24' AND user_id != 7) GROUP BY DATE(created_at) ORDER BY created_at

which produces the correct results for all videos watched each day, but as I said I want to only show each user once.

The sql I want is

SELECT COUNT(DISTINCT user_id) AS count_all, DATE(created_at) AS date_created FROM `user_video_watchings` WHERE (created_at >= '2013-01-27 10:33:18' AND user_id != 7) GROUP BY DATE(created_at) ORDER BY created_at

so i thought

UserVideoWatching.where("created_at >= ? 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')

would do what I wanted. But this gives

[#<UserVideoWatching >, #<UserVideoWatching >]

rather than a hash.

Any ideas?

I'm using rails 3.1 and mysql

score 39 · Accepted Answer · edited Jun 12 '23 at 11:37

39

You can use distinct.count(:attribute_name).

(In Rails 3 use: count(:user_id, distinct: true) instead)

Thus:

UserVideoWatching.
  where("created_at >= ? AND user_id != ?", 1.month.ago, User.elephant.id).
  group("DATE(created_at)").
  reorder('created_at').
  distinct.
  count(:user_id)

Not able to test but I think that'll produce the SQL you're after.

edited Jun 12 '23 at 11:37

duhaime

25,611
17
169
224

answered Feb 27 '13 at 11:17

Mike Campbell

7,921
2
38
51

3

deprecated in rails 4 see http://stackoverflow.com/a/19362288/670433 for the solution which is to use ModelName.distinct.count(:attribute_name) – s2t2 Feb 07 '14 at 22:39
How do you return all of the attributes of the model? – zero_cool Oct 09 '14 at 18:20

score 19 · Answer 2 · answered Oct 12 '15 at 20:00

In Rails 4, using (...).uniq.count(:user_id) as mentioned in other answers (for this question and elsewhere on SO) will actually lead to an extra DISTINCT being in the query:

SELECT DISTINCT COUNT(DISTINCT user_id) FROM ...

What we actually have to do is use a SQL string ourselves:

(...).count("DISTINCT user_id")

Which gives us:

SELECT COUNT(DISTINCT user_id) FROM ...

score 1 · Answer 3 · edited May 14 '18 at 15:18

Should use distinct, in rails 5.0.1, distinct equal uniq, but

[11] pry(main)> Needremember.distinct.count(:word)
(1.1ms)  SELECT DISTINCT COUNT(DISTINCT "needremembers"."word") FROM "needremembers"
[12] pry(main)> Needremember.uniq.count(:word)
DEPRECATION WARNING: uniq is deprecated and will be removed from Rails 5.1 (use distinct instead) (called from __pry__ at (pry):12)
   (0.6ms)  SELECT DISTINCT COUNT(DISTINCT "needremembers"."word") FROM "needremembers"

rails COUNT SELECT DISTINCT

3 Answers3

Linked