Rails ActiveRecord Perform Group, Sum and Count in one query

rails-activerecord, ruby, ruby-on-rails

Solution

Since ActiveRecord does not support multiple aggregation functions in the same query, you need to do a bit of raw SQL to achieve this.

grouped_sales = OrderType
  .select('order_types.id, order_types.name, 
     sum(orders.value) as sale, count(*) as purchase')
  .join('JOIN orders ON orders.order_type_id = order_types.id')
  .group('order_types.id')

The point to note here is that you need to use an existing column in OrderType as the alias for your aggregated columns. Here you will get the OrderType object as well.

To access the result:

id -> grouped_sales.first.id
name -> grouped_sales.first.name
total orders -> grouped_sales.first.sale
order value -> grouped_sales.first.purchase

Problem

I have two tables, ``` Order (ID, Value) ``` and ``` OrderType (ID, Name [Quote, Sale, Purchase, etc]) ``` I want to get the total number of orders in each type (count) and the total value of those orders per type (sum) I can get these individually using ``` Order.group(:order_type).count(:id) ``` and ``` Order.group(:order_type).sum(:value) ``` I would like to perform these in one query, the equivalent to the following SQL ``` SELECT order_types.id, Count(*) as total_count, Sum(orders.value) As total_value FROM order JOIN order_types ON orders.order_type_id = order_types.ID GROUP BY order_types.id ``` The query should also return the full `OrderType` object so I can display the name in my view

Original source