# How to do select with sum?

**URL:** <https://www.ruby-forum.com/t/how-to-do-select-with-sum/166949>\
**Category:** Rails\
**Created:** [May 1, 2009, 9:42am UTC](https://www.ruby-forum.com/t/how-to-do-select-with-sum/166949 "2009-05-01T09:42:41Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![miga](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/57b2e6/32.png) [@miga](https://www.ruby-forum.com/u/miga)\
**Post date:** [May 1, 2009, 9:42am UTC](https://www.ruby-forum.com/t/how-to-do-select-with-sum/166949/1 "2009-05-01T09:42:41Z")

</div>

Is there a way to rewrite the following Model method avoiding  
connection.select?

class LineOrder \< ActivateRecord::Base  
belongs\_to :auction  
belongs\_to :operation

…  
def self.amounts\_by\_year(search)  
search\_company = “”  
if search  
search\_company = "inner join auctions on  
line\_orders.auction\_id=auctions.id where company LIKE "  
+ “’%#{search}%’”  
end  
connection.select\_all("select year(date) as YD,  
sum(if(operation\_id != 2, gross\_price, NULL)) as BGP,  
sum(if(operation\_id != 2, costs, NULL)) as BC,  
sum(if(operation\_id != 2, net\_price, NULL)) as BNP,  
sum(if(operation\_id = 2, gross\_price, NULL)) as SGP,  
sum(if(operation\_id = 2, costs, NULL)) as SC,  
sum(if(operation\_id = 2, net\_price, NULL)) as SNP  
from line\_orders " + "#{search\_company} " + “group by year(date)”)  
end

To fix the idea:

The method is used in the index view to show at user’s will the  
amounts by year either of a given auction or of all auctions,  
depending if the user has chosen to restrict the list to a particular  
auction or not.

search is then the variable which is passed from the view to the model  
to get the scope on which the method will be applied.

company is a field of the auction Model.

gross\_price, costs, net\_price are entered unsigned for user’s  
convenience. Hence they need to be separated afterwards depending on  
the operation\_id.

---

<div class="post-metadata">

**Author:** ![miga](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/57b2e6/32.png) [@miga](https://www.ruby-forum.com/u/miga)\
**Post date:** [May 2, 2009, 1:44am UTC](https://www.ruby-forum.com/t/how-to-do-select-with-sum/166949/2 "2009-05-02T01:44:25Z")

</div>

I am interested in this also.  
Sorry don’t have any ideas as I am to new to RoR myself