Use active_record_calculator to perform subgrouping of aggregate calculations

Example:

calculator = Purchases.calculator(:conditions => "created_at > '2011-07-01'", :group => "user_id") do |c|
  c.count :id, "total"
  c.cnt :id, "expensive", "price > 100" #cnt is an alias for count
  c.sum :price, "cheap_spending", "price < 100"
  c.avg :price, "cheap_average", "price < 100" #avg is an alias for average
  c.min :price, "least"
  c.max :price, "most" #max is an alias for maximum
  c.col :item_name #col is an alias for column; adds a column to the result
end

calculator.calculate

OR

calculator = Purchases.calculator(:conditions => "created_at > '2011-07-01'", :group => "user_id")
calculator.count :id, "total"
calculator.cnt :id, "expensive", "price > 100"
calculator.sum :price, "cheap_spending", "price < 100"
calculator.avg :price, "cheap_average", "price < 100"
calculator.min :price, "least"
calculator.max :price, "most"
calculator.calculate

When adding operations, the calculator expects the format

method(column, alias, sub_group_condition)

The calculate method yields an array of hashes with the aliases as keys and results as values Group columns are automatically included

Example:

>> calc = Transaction.calculator(:conditions => “transactions.user_id = 55555”, :group => “transactions.user_id, bonus”) do |c| ?> c.count :id, “transactions_count” >> c.count :id, “approved_bonus_count”, “status = ‘approved’ and bonus = true” >> c.count :id, “approved_offers_count”, “status = ‘approved’ and bonus = false” >> end … >> calc.calculate

> [“group_column_1”=>“55555”, “group_column_2”=>“0”, “transactions_count”=>34, “approved_offers_count”=>0, “group_column_1”=>“55555”, “group_column_2”=>“1”, “transactions_count”=>18, “approved_offers_count”=>17]

You can use statement to see the sql created

The updater can be used for fast direct sql updates. An updater needs to be created where all the operations have aliases that have a respective column name in the update table. The first two arguments are the update table and the key to join. The key is joined with the first group column which is also required.

updater = Purchases.updater(:purchase_history, :user_id, :conditions => "created_at > '2011-07-01'", :group => "user_id")
updater.count :id, "total_purchases"
updater.cnt :id, "expensive_purchases", "price > 100"
updater.sum :price, "cheap_spending", "price < 100"
updater.avg :price, "cheap_average", "price < 100"
updater.min :price, "least_purchase"
updater.max :price, "most_purchase"

Update should work with all ActiveRecord supported databases except sqlite