Rails - Jan 2006 - How to use MySQL sum() to get total of column?

Easy one: Let''s say I have a table called ''employees''
with a column
called salaries, how can I add Employee.salaries_total to the model? 
Thanks!

-- 
Posted via http://www.ruby-forum.com/.

On 1/2/06, Sean Schertell <sean@datafly.net>
wrote:
> Easy one: Let''s say I have a table called
''employees'' with a column
> called salaries, how can I add Employee.salaries_total to the model?
> Thanks!

class Employee < ActiveRecord::Base
# other stuff here

  def self.salaries_total
    self.connection.select_value(''select sum(salaries) from
employees'').to_f
  end

end

Employee.connection.select_value() returns a single value from a SQL statement.
For functions, like sum(), avg(), etc, it can''t guess what kind of
value it is, so it returns a string.  You want it as a float, so
that''s why .to_f is there at the end.

That''s exactly what I needed!  Thanks!

-- 
Posted via http://www.ruby-forum.com/.

On Jan 1, 2006, at 9:19 PM, Sean Schertell wrote:
> Easy one: Let''s say I have a table called
''employees'' with a column
> called salaries, how can I add Employee.salaries_total to the model?
As follows:
   def sum
     @dealings = Employee.find(:all)
     sum = 0
     @dealings.each { |x| sum = sum+x.salaries }
     flash[:notice]="Sum of salaries is''
''+(sum.to_s % " %.2f")+''. ''
     redirect_to :action =>''list''
   end

I know I could have used the MySQL sum() function, but didn''t because  
I want to keep the logic separated from the database as much as  
possible.
Cheers,
Hasan Diwan <hasan.diwan@gmail.com>

-------------- next part --------------
A non-text attachment was scrubbed...
Name: PGP.sig
Type: application/pgp-signature
Size: 186 bytes
Desc: This is a digitally signed message part
Url :
http://wrath.rubyonrails.org/pipermail/rails/attachments/20060102/c08901b6/PGP-0001.bin

On 1/2/06, Hasan Diwan <hasan.diwan@gmail.com>
wrote:
>
> On Jan 1, 2006, at 9:19 PM, Sean Schertell wrote:
>
> > Easy one: Let''s say I have a table called
''employees'' with a column
> > called salaries, how can I add Employee.salaries_total to the model?
> As follows:
>    def sum
>      @dealings = Employee.find(:all)
>      sum = 0
>      @dealings.each { |x| sum = sum+x.salaries }
>      flash[:notice]="Sum of salaries is''
> ''+(sum.to_s % " %.2f")+''. ''
>      redirect_to :action =>''list''
>    end
>
> I know I could have used the MySQL sum() function, but didn''t
because
> I want to keep the logic separated from the database as much as
> possible.
> Cheers,
> Hasan Diwan <hasan.diwan@gmail.com>
Even if you want to avoid sum(), you probably don''t really want to
instantiate an Employee object for every employee, just to add things
up.  I''d vote for:

class Employee < ActiveRecord::Base
  def self.total_salaries
    salaries = self.connection.select_values(''select salaries from
employees'')
    salaries.inject(0) {|sum, amount| sum + amount.to_f}
  end
end

..and then deal with the formatting, flash[:notice], etc, in whatever
controller action happens to call Employee.total_salaries
"inject" goes through the list of salaries one by one. 
"sum" starts
out at 0, and each amount is added to it in turn, eventually returning
the total.

You can''t necessarily trust select_values to return something other
than a string here, depending on the database.. that''s why amount has
''to_f'' called on it.

Beware of this method if you want accuracy.

If the salary column is converted to float by AR, then you are
accumulating floats, which can introduce small errors depending on the
overall magnitude of the sums. (Well known float problem).

If you were to use the sum function within mysql (or postgresql) and the
column is a decimal(6,2) for instance, then these float based errors
will not happen.

Hasan Diwan wrote:
>
> On Jan 1, 2006, at 9:19 PM, Sean Schertell wrote:
>
>> Easy one: Let''s say I have a table called
''employees'' with a column
>> called salaries, how can I add Employee.salaries_total to the model?
>
> As follows:
>   def sum
>     @dealings = Employee.find(:all)
>     sum = 0
>     @dealings.each { |x| sum = sum+x.salaries }
>     flash[:notice]="Sum of salaries is''
> ''+(sum.to_s % " %.2f")+''. ''
>     redirect_to :action =>''list''
>   end
>
> I know I could have used the MySQL sum() function, but didn''t
because
> I want to keep the logic separated from the database as much as 
> possible.
> Cheers,
> Hasan Diwan <hasan.diwan@gmail.com>
>
>------------------------------------------------------------------------
>
>_______________________________________________
>Rails mailing list
>Rails@lists.rubyonrails.org
>http://lists.rubyonrails.org/mailman/listinfo/rails
>  
>