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!