activeRecord Sum

rubyonrails-talk
#1

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

#2

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]

#3

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

#4

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.

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.

#5

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?