Forum: Ruby on Rails Using find_by_sql to get the sum of a 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.
Richard D. (Guest)
on 2006-01-07 02:02
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!
Wilson B. (Guest)
on 2006-01-07 08:17
(Received via mailing list)
On 1/6/06, richard downe <removed_email_address@domain.invalid> 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
Alain R. (Guest)
on 2006-01-07 12:35
(Received via mailing list)
Richard

As an alternative, have a look at the CalculationsPlugin;

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

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

(note: I haven't used it yet)


Alain
Richard D. (Guest)
on 2006-01-08 00:50
Alain R. wrote:
> Richard
>
> As an alternative, have a look at the CalculationsPlugin;
>
>       http://techno-weenie.net/blog/main/266/introducing...
>
> http://techno-weenie.net/blog/code/269/more-on-act...
>
> (note: I haven't used it yet)
>
>
> Alain

Thanks to both of you for the help!
Jérôme L (Guest)
on 2006-01-09 00:03
(Received via mailing list)
On 1/7/06, richard downe <removed_email_address@domain.invalid> 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 !
Rick O. (Guest)
on 2006-01-09 00:51
(Received via mailing list)
> 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
Damon C. (Guest)
on 2006-01-09 01:52
(Received via mailing list)
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/
Rick O. (Guest)
on 2006-01-09 04:34
(Received via mailing list)
> 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
Damon C. (Guest)
on 2006-01-09 04:52
(Received via mailing list)
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
This topic is locked and can not be replied to.