Ruby on Rails/ActiveRecord/Calculations

Calculations

Calculations provide methods for calculating aggregate values of columns in ActiveRecord models.

Calculate

All calculations are handled through the calculate method. The calculate method accepts the name of the operation, the column name and any options. The options can be used to customize the query with :conditions, :order, :group, :having and :joins.

The supported calculations are:

• average
• sum
• minimum
• maximum
• count

The calculate method has two modes of working. If the :group option is not set then the result will be returned as a single numeric value (fixnum for count, float for average and the column type for everything else). If the :group option is set then the result is returned as an ordered Hash of the values and groups them by the :group column. The :group option takes either a column name or the name of a belongs_to association.

Note that if a condition specified in the calculation results in no values returned from the underlying table then the calculate method will return nil.

For example:

 values = Person.maximum(:age, :group => 'last_name')
 puts values["Drake"]
 => 43

 drake  = Family.find_by_last_name('Drake')
 values = Person.maximum(:age, :group => :family) # Person belongs_to :family
 puts values[drake]
 => 43

 values.each do |family, max_age|
    ...
 end

Average

You can use the average method to calculate the average value for a particular column. For example:

 Person.average(:age)

Will return the average age of all people in the Person model.

An example of customizing the query:

 Person.average(:age, :conditions => ['age >= ?', 55])

This would return the average age of people who are 55 or older.

Sum

The sum method will calculate the sum for a particular column. For example:

 Product.sum(:number_in_stock)

Will return the sum of products in stock.

An example of customizing the query:

 Product.sum(:number_in_stock, :conditions => ['category_id = ?', 10])

Will return the sum of the number of products which are in category 10 and in stock.

Minimum

The minimum method will calculate the minimum value for a particular column. For example:

 Donation.minimum(:amount)

Will return the lowest amount donated.

An example of customizing the query:

 Donation.minimum(:amount, :conditions => ['created_at > ?', 1.year.ago])

This will return the lowest amount donated within the last year.

Maximum

The maximum method will calculate the maximum value for a particular column. For example:

 Donation.maximum(:amount)

Will return the largest amount donated.

An example of customizing the query:

 Donation.maximum(:amount, :conditions => ['created_at > ?', 1.year.ago])

This will return the largest amount donated within the last year.

Count

Counts the number of items matching the condition(s).

 TestResult.count(:all)

Will return the number of TestResult objects in the database.

An example of customizing the query:

 TestResult.count(:all,:conditions=>['starttime>=?',Time.now-3600*24])

Will return the number of TestResult objects whose starttime field is within the last 24 hours.