How can I count the number of records that have a unique value in a specific field in a ROR?

Question

I have a recordset that includes a date field, and you want to determine how many unique dates are represented in the recordset.

Something like:

Record.find(:all).date.unique.count 

but of course this does not work.

Answer 1

Why do we need the following SQL:

 SELECT COUNT(DISTINCT date) FROM records 

ActiveRecord has a built-in function:

 Record.count('date', :distinct => true) 

Answer 2

This has changed slightly in rails 4 and above :distinct => true now deprecated. Using:

 Record.distinct.count('date') 

Or if you need a date and number:

 Record.group(:date).distinct.count(:date) 

Answer 3

Beyond SQL:

 Record.find(:all).group_by(&:date).count 

ActiveSupport The variable # group_by is indispensable.

Answer 4

As I mentioned here , 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 additional DISTINCT found in the request:

SELECT DISTINCT COUNT(DISTINCT user_id) FROM ...

We really need to use the SQL string:

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

What gives us:

SELECT COUNT(DISTINCT user_id) FROM ...

Answer 5

Detail answer:

 Post.create(:user_id => 1, :created_on => '2010-09-29') Post.create(:user_id => 1, :created_on => '2010-09-29') Post.create(:user_id => 2, :created_on => '2010-09-29') Post.create(:user_id => null, :created_on => '2010-09-29') Post.group(:created_on).count # => {'2010-09-29' => 4} Post.group(:created_on).count(:user_id) # => {'2010-09-29' => 3} Post.group(:created_on).count(:user_id, :distinct => true) # Rails <= 3 Post.group(:created_on).distinct.count(:user_id) # Rails = 4 # => {'2010-09-29' => 2} 

Answer 6

the last #count in the rails source code takes only 1 parameter. see http://api.rubyonrails.org/classes/ActiveRecord/Calculations.html#method-i-count

so I reached this requirement on

 Record.count('DISTINCT date') 

Answer 7

Also, make sure you have an index in the field in your db, otherwise this query will quickly become sloooow.

(This is much better done in SQL, otherwise you will pull the entire db table into memory to answer the score.)