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