Rails - Jan 2006 - Using find_by_sql to get the sum of a column

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!

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

On 1/6/06, richard downe <rmdowne@yahoo.com>
wrote:
> 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.
>
class Score < ActiveRecord::Base
  def self.total_points
    connection.select_value("select sum(score) from scores").to_i
  end
end

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

Alain Ravet 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!

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

On 1/7/06, richard downe <rmdowne@yahoo.com> wrote:
> 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.
def self.total_points
  @points = self.count_by_sql("SELECT SUM(score) FROM scores")
end

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

> 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

richard downe <rmdowne@...> writes:
> 	 <at> 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
http://damonclinkscales.com/

> 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

Rick Olson <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