Aggregate functions and your ActiveRecord app

When trying to calculate the sum across all database model records for a given attribute, there are a few things I suggest considering when designing your code:

1. understand SQL aggregate methods; leverage the database
2. understand ActiveRecord's convenience methods for executing queries with aggregate methods
3. when all else fails, use Ruby to collect data

using SQL to get the sum

Here are some references that should provide an introduction to SQL aggregate functions and how they are used:

Home → Documentation → Manuals → PostgreSQL 8.2
http://www.postgresql.org/docs/8.2/static/functions-aggregate.html

W3Schools.com
http://www.w3schools.com/sql/sql_func_sum.asp

Tech on the net
http://www.techonthenet.com/sql/sum.php

 

using ActiveRecord to perform the sum

Once you have an understanding of how SQL is used to perform the sum, you can use ActiveRecord as a convenience tool to get what you want.

Ruby on Rails v4.0.0
Module ActiveRecord::Calculations
http://api.rubyonrails.org/classes/ActiveRecord/Calculations.html#method-i-sum

examples

sum column for all records in a given table

Ucpathpersonjob.sum(:emplrecord)

isolate the records of interest; sum only the returned records

Ucpathpersonjob.where("emplrecord < 7000").sum(:emplrecord)

Although it is not as efficient when compared with how the database performs aggregate functions like sum(), it can be done with Ruby.

using Ruby to perform the sum

Provide a single block parameter for each item in the array.  Also, you need to store the sum value outside of your #each block to sum up the values.

I am assuming you wish to sum up the :price attribute of your model.  The example should explain how you access the model attribute as you also asked.

sum_value = 0
Expense.all.each do |record|
  sum_value = sum_value + record.price
end

a more concise approach

• a represents the compilation for the entire process
• b is each item in the array

with plain integers

1 + 2 + 3 + 4 + 5 + 6 + 67 + 7 + 8 + 8 + 9 + 90 + 23
 => 233

using #inject

[1, 2, 3, 4, 5, 6, 67, 7, 8, 8, 9, 90, 23].inject(0){|a,b| b += a}
 => 233

applying to your model

Expense.all.inject(0){|expense, sum| sum += expense.price}

For more info on Array#inject see http://blog.jayfields.com/2008/03/ruby-inject.html