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

on 2007-03-09 03:07

on 2007-03-09 03:24

> 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!!

on 2007-03-09 04:12

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]

on 2007-03-09 09:00

> 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 ;) regards javier -- -------- Estamos de estreno... si necesitas llevar el control de tus gastos visita http://www.gastosgem.com !!Es gratis!!

on 2007-03-09 16:04

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

on 2007-03-09 16:49

> > > > > 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.

on 2007-03-09 17:05

David Coleman 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?

on 2007-03-09 17:59

Eden Li wrote: > David Coleman 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. :-) Thanks!

on 2007-03-09 21:32

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 Coleman wrote: > Eden Li wrote: >> David Coleman 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. :-) > Thanks!