• dann
  • Coach Class
  • Offline
  • Registered: 2009-01-29
  • Posts: 67

Topic: Any way to do a group by/sum call on ActiveRecord?

What I want to do is group by category and then sum of the aggregate values of the records in those categories...so if I had an Animal model with records of category Dog and Cat with weights...I want to find the total weight for both Cats and Dogs. I can think of a way to do it with custom sql or multiple queries, such as: a 'group by' to get an array of the different categories, and then a do loop that does something like Animal.sum("weight", :conditions=>{"category=?", animaltype}). But I was wondering if there was a more elegant way to do it.

  • From: Dallas/Fort Worth, Texas
  • Registered: 2008-06-06
  • Posts: 64

Re: Any way to do a group by/sum call on ActiveRecord?

Is Max's solution what you're looking for? 

http://railsforum.com/viewtopic.php?id=27205

  • From: Germany
  • Registered: 2007-07-02
  • Posts: 4,011

Re: Any way to do a group by/sum call on ActiveRecord?

@categories = Categories.find(:all, :joins => :animals,
                              :select => "categories.*, SUM(animals.weight) as weight_sum",
                              :group => "categories.id")
# ATTENTION: weight_sum is now a temporary attribute of the categories returned!
# and the animals are NOT eager-loaded
<% @categories.each do |c| %>
  Category: <%= c.name %><br />
  Sum of Weight in this category: <%= c.weight_sum %><br />
<% end %>

  • dann
  • Coach Class
  • Offline
  • Registered: 2009-01-29
  • Posts: 67

Re: Any way to do a group by/sum call on ActiveRecord?

Thanks for the help...both those solutions probably work, though I wrote this while waiting for suggestions:

Animal.all(:group=>"category").collect{|c| [c.category, Animal.sum("weight", :conditions=>"category='#{c.category}'")] }

Returns a 2D array in the form of [["Animal Type", AnimalWeight]]

The line of code is about as long as Duplex's, but I think Duplex's result is more sensical.

  • From: Germany
  • Registered: 2007-07-02
  • Posts: 4,011

Re: Any way to do a group by/sum call on ActiveRecord?

probably the more important part is that mine is only 1 SQL query, while yours requires one SQL query to get the categories, and the one for each category, which is not very nice, from a performance-perspective.

  • dann
  • Coach Class
  • Offline
  • Registered: 2009-01-29
  • Posts: 67

Re: Any way to do a group by/sum call on ActiveRecord?

Yes...which is why I asked the question because I knew someone would have the less costly arrangement...thanks again.

  • Registered: 2009-09-11
  • Posts: 2

Re: Any way to do a group by/sum call on ActiveRecord?

Great question, great answers.  I used the Duplex solution.  Saved me a lot of time.