Rails ActiveRecord Perform Group, Sum and Count in one query

Question

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

Answer 1

There is even better solution, just:

.pluck('sum(orders.value), count(*)').first

Answer 2

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