Forum: Ruby on Rails How to use MySQL sum() to get total of column?

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Sean S. (Guest)
on 2006-01-02 07:19
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!
Wilson B. (Guest)
on 2006-01-02 09:47
(Received via mailing list)
On 1/2/06, Sean S. <removed_email_address@domain.invalid> 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.
Sean S. (Guest)
on 2006-01-02 14:04
That's exactly what I needed!  Thanks!
Hasan D. (Guest)
on 2006-01-02 19:58
(Received via mailing list)
On Jan 1, 2006, at 9:19 PM, Sean S. 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 D. <removed_email_address@domain.invalid>
Wilson B. (Guest)
on 2006-01-02 21:30
(Received via mailing list)
On 1/2/06, Hasan D. <removed_email_address@domain.invalid> wrote:
>      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 D. <removed_email_address@domain.invalid>

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.
Jim M. (Guest)
on 2006-01-02 23:45
(Received via mailing list)
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.
This topic is locked and can not be replied to.