# MySQL - How to “SUM” in a :has\_many case

**URL:** <https://www.ruby-forum.com/t/mysql-how-to-a-oesuma-in-a-has-many-case/191230>\
**Category:** Rails\
**Created:** [July 7, 2010, 8:14pm UTC](https://www.ruby-forum.com/t/mysql-how-to-a-oesuma-in-a-has-many-case/191230 "2010-07-07T20:14:38Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![tharrisx](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/e19adc/32.png) [@tharrisx](https://www.ruby-forum.com/u/tharrisx)\
**Post date:** [July 7, 2010, 8:14pm UTC](https://www.ruby-forum.com/t/mysql-how-to-a-oesuma-in-a-has-many-case/191230/1 "2010-07-07T20:14:38Z")

</div>

Hi there,

I have the following tables…

User :has\_many Purchases  
Item :has\_many Purchases

…where Item has a column “amount” (can be + or -) and I need to find  
all Users that have a positive SUM of “amounts” (over all Purchases each  
one has made).

How does this query look like? (I’m not sure how to handle “SUM”  
correctly, in this case.)

I started out with the following, but obviously, it’s wrong… (it  
wouldn’t “include” Purchases that have an Item with a negative  
“amount”…)

@users = User.find(:all,  
:include =\> {:purchases =\> :item},  
:select =\> “SUM(item.amount)”,  
:order =\> “…”,  
:conditions =\> “…”,  
:group =\> “users.id”,  
:having =\> “SUM(item.amount) \> 0” )

Thanks for your help with this!  
Tom

---

<div class="post-metadata">

**Author:** ![tharrisx](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/e19adc/32.png) [@tharrisx](https://www.ruby-forum.com/u/tharrisx)\
**Post date:** [July 7, 2010, 10:41pm UTC](https://www.ruby-forum.com/t/mysql-how-to-a-oesuma-in-a-has-many-case/191230/2 "2010-07-07T22:41:37Z")

</div>

Tom Ha wrote:

> Hi there,
> 
> I have the following tables…
> 
> User :has\_many Purchases  
> Item :has\_many Purchases
> 
> …where Item has a column “amount” (can be + or -) and I need to find  
> all Users that have a positive SUM of “amounts” (over all Purchases each  
> one has made).
> 
> How does this query look like? (I’m not sure how to handle “SUM”  
> correctly, in this case.)

@user # assume this exists  
total = Purchase.sum(:amount, :conditions =\> { ‘user\_id’ =\> @user })  
=\> SELECT sum(“purchases”.amount) AS sum\_amount FROM “purchases” WHERE  
(“purchases”.“user\_id” = 1)