CODEWITHSUNDEEP
ruby-on-railsgroup-by
bopritchard
bopritchard

Reputation: 399

multi column grouping and totaling in rails

my data looks like this

 ___________________________________
|name  date      status    location |
|Tom   1/1/2010  Ready     Home     |
|Tom   1/1/2010  Ready     Home     |
|Bill  1/18/2010 Go        Work     |
|Bob   2/19/2010 Ready     Field    |
|Bob   2/19/2010 Ready     Field    |
|Wil   4/5/2010  Steady    Work     |
|Wil   4/5/2010  Steady    Work     |
|Wil   4/5/2010  Steady    Work     |
|Wil   4/5/2010  Steady    Home     |
|Bill  7/14/2010 Stop      Home     |
|___________________________________|

i need to group/count it so that looks like this

Name   Date      Location    Status   Count
Tom    1/1/2010  Home        Ready    2
Bill   1/18/2010 Work        Go       1
Bill   7/14/2010 Home        Stop     1
Bob    2/19/2010 Field       Ready    2
etc.....

Upvotes: 0

Views: 2052

Answers (1)

adamlamar
adamlamar

Reputation: 4927

Sometimes the best way to do this is tricky based upon the format and properties of your data. But in general, you can use the :group argument of ActiveRecord#find.

Model.all(:select => 'COUNT(*) AS count, name, date, location, status', :group => 'name')

Or if using Rails 3

Model.all.select('COUNT(*) AS count, name, date, location, status').group('name')

If you always intend to group the data based on this relationship, consider adding it as a default scope to your model.

class Model < ActiveRecord::Base
  default_scope select('COUNT(*) AS count, name, date, location, status')
  default_scope group('name')
end

Upvotes: 2

Related Questions