CODEWITHSUNDEEP
ruby-on-rails
DFectuoso
DFectuoso

Reputation: 4897

Counting and grouping at the same time

I have a Mail model with the following schema:

t.string   "mail"
t.integer  "country"
t.boolean  "validated"
t.datetime "created_at"
t.datetime "updated_at"

And I want to find the top 5 countries in the database, so i go ahead and type

@top5 = Mail.find(:all,:group =>  'country',:conditions => [ "validated = ?" , "t" ], :limit => 5 )

This will tell me the groups(i need an order by i dont know how to write)

@top5 = Mail.count(:all,:group =>  'country',:conditions => [ "validated = ?" , "t" ], :limit => 5 )

This will tell me how many mails are in each group

Im wondering if i can group and count in just one go

Upvotes: 15

Views: 33873

Answers (4)

Rahul Sagore
Rahul Sagore

Reputation: 1658

I too had to group data with city name and showing count of how many rows are there for each city with specific condition. So this is how I did:

CityData.where(:status => 1).group(:city_name).count

Output of this was:

{:Mumbai => 10, :Dublin => 7, :SF => 9}

Upvotes: 0

tee
tee

Reputation: 4409

With Rails 3 you can simplify it further:

Mail.where(validated: true).count(group: :country)

You can order by fields in the group - in this case only :country would be valid:

Mail.where(validated: true)
    .order(:country)
    .count(group: :country)

You can also order by the count, using "count_all":

Mail.where(validated: true)
    .order("count_all desc")
    .count(group: :country)

You can also limit the number of groups returned. To do this you must call limit before calling count (because #count returns ActiveSupport::OrderedHash):

Mail.where(validated: true)
    .order("count_all desc")
    .limit(5)
    .count(group: :country)

Updated syntax for Rails 4:

Mail.where(validated: true)
    .group(:country)
    .count

Upvotes: 22

airportyh
airportyh

Reputation: 22668

Mail.find(
    :all, 
    :select => 'count(*) count, country', 
    :group => 'country', 
    :conditions => ['validated = ?', 't' ], 
    :order => 'count DESC',
    :limit => 5)

This should give you records that have a country attribute and a count attribute.

Upvotes: 19

Can Berk Güder
Can Berk Güder

Reputation: 113300

Try:

Mail.count(:group => 'country', :conditions => ['validated = ?', 't'])

I'm not sure count accepts :limit though.

EDIT:

I think this is more readable:

Mail.count(:group => :country, :conditions => {:validated => true})

Upvotes: 24

Related Questions