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.
11a6596e5453443c025f2d23a5d06027?d=identicon&s=25 Richard Downe (runrunshaw)
on 2006-01-07 01: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!
25e11a00a89683f7e01e425a1a6e305c?d=identicon&s=25 Wilson Bilkovich (Guest)
on 2006-01-07 07:17
(Received via mailing list)
On 1/6/06, richard downe <rmdowne@yahoo.com> 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
0091f92762685860109bbcb02edfdf27?d=identicon&s=25 Alain Ravet (Guest)
on 2006-01-07 11: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
11a6596e5453443c025f2d23a5d06027?d=identicon&s=25 Richard Downe (runrunshaw)
on 2006-01-07 23:50
Alain Ravet 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!
E6dcb4743b83392a4fc39693f110cc26?d=identicon&s=25 Jérôme L (Guest)
on 2006-01-08 23:03
(Received via mailing list)
On 1/7/06, richard downe <rmdowne@yahoo.com> 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 !
821395fe70906c8290df7f18ac4ac6cf?d=identicon&s=25 Rick Olson (Guest)
on 2006-01-08 23: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
97a7959f0d9e6c90ddae200520e93067?d=identicon&s=25 Damon Clinkscales (Guest)
on 2006-01-09 00: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/
821395fe70906c8290df7f18ac4ac6cf?d=identicon&s=25 Rick Olson (Guest)
on 2006-01-09 03: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
97a7959f0d9e6c90ddae200520e93067?d=identicon&s=25 Damon Clinkscales (Guest)
on 2006-01-09 03:52
(Received via mailing list)
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
This topic is locked and can not be replied to.