CODEWITHSUNDEEP
ruby-on-railsactiverecord
Joy
Joy

Reputation: 4483

Sum on multiple columns with Activerecord

I am new to Activerecord. I want to do sum on multiple columns of a model Student. My model student is like following:

 class Student < ActiveRecord::Base
   attr_accessible :class, :roll_num, :total_mark, :marks_obtained, :section
 end

I want something like that:

 total_marks, total_marks_obtained = Student.where(:id=>student_id).sum(:total_mark, :marks_obtained)

But it is giving following error. 

NoMethodError: undefined method `except' for :marks_obtained:Symbol

So I am asking whether I have to query the model two times for the above, i.e. one to find total marks and another to find marks obtained.

Upvotes: 24

Views: 32959

Answers (6)

anka
anka

Reputation: 3857

Recently, I also had the requirement to sum up multiple columns of a ActiveRecord relation. I ended up with the following (reusable) scope:

scope :values_sum, ->(*keys) {
  summands = keys.collect { |k| arel_table[k].sum.as(k.to_s) }
  select(*summands)
}

So, having a model e.g. Order with columns net_amount and gross_amount you could use it as follows:

o = Order.today.values_sum(:net_amount, :gross_amount)

o.net_amount     # -> sum of net amount
o.gross_amount   # -> sum of gross amount

Upvotes: 1

willyab
willyab

Reputation: 325

Similar to the accepted answer, however, I'd suggest using arel as follows to avoid string literals (apart from renaming columns, if needed).

Student
  .where(id: student_id).
  .where(Student.arel_table[:total_mark].sum, Student.arel_table[:marks_obtained].sum)

which will give you an ActiveRecord::Relation result over which you can iterate, or, as you'll only get one row, you can use .first (at least for mysql).

Upvotes: 1

YasirAzgar
YasirAzgar

Reputation: 1451

If you just want sum of columns total_marks and marks_obtained, try this

Student.where(:id=>student_id).sum('total_mark + marks_obtained')

Upvotes: 21

AlexGuti
AlexGuti

Reputation: 3243

You can use pluck to directly obtain the sum:

Student.where(id: student_id).pluck('SUM(total_mark)', 'SUM(marks_obtained)')
# SELECT SUM(total_mark), SUM(marks_obtained) FROM students WHERE id = ?

You can add the desired columns or calculated fields to pluck method, and it will return an array with the values.

Upvotes: 30

Marc Greenstock
Marc Greenstock

Reputation: 11668

Another method is to ActiveRecord::Calculations.pluck then Enumerable#sum on the outer array and again on the inner array pair:

Student
  .where(id: student_id)
  .pluck(:total_mark, :marks_obtained)
  .map(&:sum)
  .sum

The resulting SQL query is simple:

SELECT "students"."total_mark",
       "students"."marks_obtained"
FROM "students"
WHERE "students"."id" = $1

The initial result of pluck will be an array of array pairs, e.g.:

[[10, 5], [9, 2]]

.map(&:sum) will run sum on each pair, totalling the pair and flattening the array:

[15, 11]

Finally .sum on the flattened array will result in a single value.

Edit:

Note that while there is only a single query, your database will return a result row for each record matched in the where. This method uses ruby to do the totalling, so if there are many records (i.e. thousands), this may be slower than having SQL do the calculations itself like noted in the accepted answer.

Upvotes: 3

penner
penner

Reputation: 2737

You can use raw SQL if you need to. Something like this to return an object where you'll have to extract the values... I know you specify active record!

Student.select("SUM(students.total_mark) AS total_mark, SUM(students.marks_obtained) AS marks obtained").where(:id=>student_id)

For rails 4.2 (earlier unchecked)

Student.select("SUM(students.total_mark) AS total_mark, SUM(students.marks_obtained) AS marks obtained").where(:id=>student_id)[0]

NB the brackets following the statement. Without it the statement returns an Class::ActiveRecord_Relation, not the AR instance. What's significant about this is that you CANNOT use first on the relation.

....where(:id=>student_id).first #=> PG::GroupingError: ERROR:  column "students.id" must appear in the GROUP BY clause or be used in an aggregate function

Upvotes: 18

Related Questions