CODEWITHSUNDEEP
ruby-on-railsrubyrails-activerecord
Andrew Murphy
Andrew Murphy

Reputation: 231

Rails ActiveRecord Perform Group, Sum and Count in one query

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

Upvotes: 16

Views: 8838

Answers (3)

Alexandr
Alexandr

Reputation: 1714

Nowadays pluck+arel will do the job.

model = Model.arel_table
Model.group(:order_type).pluck(model[:id].count, model[:value].sum)

Also appending with .order(:order_type) may be needed if there applied default ordering by ID.

Upvotes: 6

Wojtek Kosak Główczewski
Wojtek Kosak Główczewski

Reputation: 265

There is even better solution, just:

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

Upvotes: 10

Raj
Raj

Reputation: 22926

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

Upvotes: 14

Related Questions