Is there any way to get a sum of a column without doing a seperate
query?
I have the following that works, but I wonder if it can be done without
performing the query twice:
@sales_orders = SalesOrder.find(:all, :conditions =>
[''month(ship_date)
= ? and year(ship_date) = ?'',@month,@year])
@total = SalesOrder.find(:all, :conditions => [''month(ship_date) = ?
and
year(ship_date) = ?'',@month,@year]).sum(&:amount)
Is there any way I can use something like
@sales_orders.sum(''amount'') or
something like that?
Thanks,
Jason
--
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
-~----------~----~----~----~------~----~------~--~---
Jason <rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:> I have the following that works, but I wonder if it can be done without > performing the query twice: > > @sales_orders = SalesOrder.find(:all, :conditions => [''month(ship_date) > = ? and year(ship_date) = ?'',@month,@year]) > > @total = SalesOrder.find(:all, :conditions => [''month(ship_date) = ? and > year(ship_date) = ?'',@month,@year]).sum(&:amount) > > > Is there any way I can use something like @sales_orders.sum(''amount'') or > something like that?If I am reading this right, yes, you should just be able to do: @sales_orders = SalesOrder.find(:all, :conditions => [''month(ship_date) = ? and year(ship_date) = ?'',@month,@year]) @total = @sales_orders.sum(&:amount) - Tyler --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
That worked! Thank you so much! -- 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 -~----------~----~----~----~------~----~------~--~---
Hi ... I think it can be done in one step with:
SalesOrder.sum(''amount'', :conditions =>
[''month(ship_date) = ? and
year(ship_date) = ?'',@month,@year])
nice if you can let SQL do the math(s)
On Feb 20, 9:33 pm, Jason
<rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>
wrote:> That worked!
>
> Thank you so much!
>
> --
> Posted viahttp://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''d normally let the database do the work, and create the appropriate
SQL statement within the controller. Off the top of my head, within
your SalesOrder controller:
def ship_date_sum(@month, @year)
sum = SalesOrder.find_by_sql("SELECT SUM(amount) FROM SalesOrders
WHERE month(ship_date) = ? AND year(ship_date) = ?", @month, @year)
return sum
end
Then just call it as SalesOrder.ship_date_sum(@month,@year) from
wherever you want to use it.
(Yep, I''m almost certain that isn''t 100% correct, but
I''m typing this
while I''m on hold for a phone call and hopefully you get the gist of
it...)
Doing the SELECT SUM(amount) ... in SQL will force the database to do
the work rather than your app, and will return a minimal amount of
data to your app. It makes it easier to optimise your database later
(e.g. work out where to put indexes, in this case), gives you easier
scalability, consumes less memory on your Rails server, ... If you''re
really pushed for performance, you could even create the SELECT SUM...
as a stored proc on your database server and get the result back
faster.
I''m all for getting the database to do as much work as possible, and
making sure my Rails code has as small a footprint as possible. Other
people prefer the Rails app to do all the work - Google for the pros
and cons of each approach if you''re interested.
YMMV
Regards
Dave M.
On 21/02/07, Jason
<rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>
wrote:>
> Is there any way to get a sum of a column without doing a seperate
> query?
>
> I have the following that works, but I wonder if it can be done without
> performing the query twice:
>
> @sales_orders = SalesOrder.find(:all, :conditions =>
[''month(ship_date)
> = ? and year(ship_date) = ?'',@month,@year])
>
> @total = SalesOrder.find(:all, :conditions =>
[''month(ship_date) = ? and
> year(ship_date) = ?'',@month,@year]).sum(&:amount)
>
>
> Is there any way I can use something like
@sales_orders.sum(''amount'') or
> something like that?
>
> Thanks,
>
> Jason
>
> --
> 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
-~----------~----~----~----~------~----~------~--~---