How to use MySQL sum() to get total of column?

Rails
1

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!

2

On 1/2/06, Sean S. [email protected] 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.

3

That’s exactly what I needed! Thanks!

4

On 1/2/06, Hasan D. [email protected] 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. [email protected]

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.

5

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. [email protected]

6

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.