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 -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
> 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!! --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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 Mar 9, 10:20 am, javier ramirez <jrami...-7iWCczGtl7hBDgjK7y7TUQ@public.gmane.org> wrote:> > 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 visitahttp://www.gastosgem.com!!Es gratis!!--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
> 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!! --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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 -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
> 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_iChange those .to_i''s to .to_f''s. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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? --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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! -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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!-- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---