Using find_by_sql to get the sum of a column

Rails
1

Hello,

I was wondering if there was a method in Rails that returns the sum of a
column. For example, I have a column called ‘score’ and writing a SQL
statement such a ‘select sum(score) from table_name’ does return the sum
of the values in the column. In the past (not too long ago being a
newbie), I defined all sorts of methods only to discover that Rails
(after reading Agile Web Dev) already had something much simpler (I
don’t mind since it’s helping me learn Ruby along the way).

My question is this, does Rails have any methods that can do this …

def self.total_points
@points = self.find_by_sql(“select sum(score) from scores”)
points = @points[0] #assigns first row
points = points.attributes #assigns as hash
points = points.values # pulls out the value
points = points.to_s #converts to string
points = points.to_i # converts to fixnum
end

Or am I wanting to be too concise. Also, if there isn’t a Rails way of
condensing the above, is there any shortcut in Ruby.

Thanks for any suggestions in advance!

2

On 1/6/06, richard downe [email protected] wrote:

My question is this, does Rails have any methods that can do this …
Or am I wanting to be too concise. Also, if there isn’t a Rails way of
condensing the above, is there any shortcut in Ruby.

class Score < ActiveRecord::Base
def self.total_points
connection.select_value(“select sum(score) from scores”).to_i
end
end

3

Richard

As an alternative, have a look at the CalculationsPlugin;

  http://techno-weenie.net/blog/main/266/introducing-calculations

http://techno-weenie.net/blog/code/269/more-on-activerecord-calculations

(note: I haven’t used it yet)

Alain

4

On 1/7/06, richard downe [email protected] wrote:

condensing the above, is there any shortcut in Ruby.
def self.total_points
@points = self.count_by_sql(“SELECT SUM(score) FROM scores”)
end

and you’ll get a fixnum, non conversion needed !

5

Alain R. wrote:

Richard

As an alternative, have a look at the CalculationsPlugin;

  http://techno-weenie.net/blog/main/266/introducing-calculations

http://techno-weenie.net/blog/code/269/more-on-activerecord-calculations

(note: I haven’t used it yet)

Alain

Thanks to both of you for the help!

6

def self.total_points
@points = self.count_by_sql(“SELECT SUM(score) FROM scores”)
end

In calculations it’s:

Score.calculate :sum, :score

Calculations really come in handy when you bring in group and having
clauses, however.

–
rick
http://techno-weenie.net

7

richard downe <rmdowne@…> writes:

points = self.find_by_sql(“select sum(score) from scores”)

The other two answers are perfectly great, but I have to add a third way
which
shows how to grab that value with find_by_sql.

def self.total_points
points = self.find_by_sql(“select sum(score) as scoresum from scores”)
return points[0].scoresum.to_i
end

-damon

8

The other two answers are perfectly great, but I have to add a third way which
shows how to grab that value with find_by_sql.

def self.total_points
points = self.find_by_sql(“select sum(score) as scoresum from scores”)
return points[0].scoresum.to_i
end

The issue with that is, find_by_sql returns a model. Why not just do
this then?

def self.total_points
connection.select_value(“select sum(score) as scoresum from
scores”).to_i
end

–
rick
http://techno-weenie.net

9

Rick O. <technoweenie@…> writes:

The issue with that is, find_by_sql returns a model. Why not just do this
then?

def self.total_points
connection.select_value(“select sum(score) as scoresum from scores”).to_i
end

Yep, if it’s just a single value, I agree. A lot of people
forget that they can rename columns and computations with “AS”
in their SQL as part of a larger query, so I mentioned it.

-damon