activeRecord Sum

Hi All,

I’m trying to get the sum of 3 database fields.

Instead of calling find, I’m using sum like this:

@itemsum = Order.sum(:item_total)
@shipsum = Order.sum(:shipping_total)
@taxsum = Order.sum(:tax_total)

But, would rather not make 3 database calls to do so.
I can’t find any documentation on sum as a finder, so was wondering if
anyone has a solution for getting the sum of multiple collumns this way.

The end sql result I’m trying to achieve is:

SELECT sum(tax_total) AS sum_tax_total, sum(item_total) AS
sum_item_total, sum(shipping_total) AS sum_shipping_total FROM orders

Of course I could just do a find_by_sql, but was just trying something
new.

Thanks!

Dave

afaik, there’s no special finder for collecting together three sums in
a single query. You’ll need to get your hands mildly dirty with SQL.
Javier points out easiest solution with one tweak – drop the colons:

sums = Order.find(:all, :select=>‘sum(item_total) AS item,
sum(shipping_total) AS ship, sum(tax_total) AS tax’).first
@itemsum = sums[:item]
@shipsum = sums[:ship]
@taxsum = sums[:tax]

I’m trying to get the sum of 3 database fields.

Instead of calling find, I’m using sum like this:

@itemsum = Order.sum(:item_total)
@shipsum = Order.sum(:shipping_total)
@taxsum = Order.sum(:tax_total)

But, would rather not make 3 database calls to do so.

maybe you can try

Order.find(:all,:select=>‘sum(:item_total),sum(:shipping_total),sum(:tax_total)’)

in case you need it you can also add ,group=>:my_grouping_field

regards,

javier ramirez


Estamos de estreno… si necesitas llevar el control de tus gastos
visita http://www.gastosgem.com !!Es gratis!!

afaik, there’s no special finder for collecting together three sums in
a single query. You’ll need to get your hands mildly dirty with SQL.
Javier points out easiest solution with one tweak – drop the colons:

right… lazy me… i c/pasted the fields into the sentence and i didn’t
realize i was taking the colons with me :wink:

regards

javier

Estamos de estreno… si necesitas llevar el control de tus gastos
visita http://www.gastosgem.com !!Es gratis!!

Eden Li wrote:

afaik, there’s no special finder for collecting together three sums in
a single query. You’ll need to get your hands mildly dirty with SQL.
Javier points out easiest solution with one tweak – drop the colons:

sums = Order.find(:all, :select=>‘sum(item_total) AS item,
sum(shipping_total) AS ship, sum(tax_total) AS tax’).first
@itemsum = sums[:item]
@shipsum = sums[:ship]
@taxsum = sums[:tax]

So, this approach gives me an inaccurate number:


sums = Order.find(:all, :select=>‘sum(item_total) AS item,
sum(shipping_total) AS ship, sum(tax_total) AS tax’).first

@were_rich = sums[:item].to_i + sums[:ship].to_i + sums[:tax].to_i

In this case, @were_rich = 197,018.00 (rounded because it’s a Float)

But, if I get the sum this way it’s accurate:

@sums = Order.find(:all)
@were_rich = @sums.sum(&:item_total) + @sums.sum(&:shipping_total) +
@sums.sum(&:tax_total)

Here, @were_rich = 197,018.77


So the first approach, I lost my 77 cents.

I guess the difference in the 2 approaches is that approach 1 lets mySQL
do the math were approach 2 lets Ruby do the math.

Thanks for your help!

Dave

So the first approach, I lost my 77 cents.

I guess the difference in the 2 approaches is that approach 1 lets mySQL
do the math were approach 2 lets Ruby do the math.

I think you’re losing the 77 cents because of this line:

@were_rich = sums[:item].to_i + sums[:ship].to_i + sums[:tax].to_i

Change those .to_i’s to .to_f’s.

David C. wrote:

@were_rich = sums[:item].to_i + sums[:ship].to_i + sums[:tax].to_i

In this case, @were_rich = 197,018.00 (rounded because it’s a Float)

Uhh… #to_i means “convert to integer”. You probably meant to use
#to_f?

Eden Li wrote:

David C. wrote:

@were_rich = sums[:item].to_i + sums[:ship].to_i + sums[:tax].to_i

In this case, @were_rich = 197,018.00 (rounded because it’s a Float)

Uhh… #to_i means “convert to integer”. You probably meant to use
#to_f?

works much better that way. :slight_smile:
Thanks!

Just so you know you could try something like this also

find_by_sql(“select (item_total + shipping_total + tax_total) as
grandtotal from tablename group by id”)

this method is more efficient

David C. wrote:

Eden Li wrote:

David C. wrote:

@were_rich = sums[:item].to_i + sums[:ship].to_i + sums[:tax].to_i

In this case, @were_rich = 197,018.00 (rounded because it’s a Float)

Uhh… #to_i means “convert to integer”. You probably meant to use
#to_f?

works much better that way. :slight_smile:
Thanks!

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs