Currently Reading

ActiveRecord SELECT SUM & SELECT SUM by CASE Methods

ActiveRecord is great. It helps hide away a lot of trivial SQL when creating applications with Rails. It’s fairly good at what it does to and it’s constantly growing and improving. However, it falls short when it comes to creating a query that combines multiple calculations. As an example, let’s get an array of the total sales for every month for the last 12 months of Orders.

This is going to run 12 queries in order to get the data. That’s 12 round trips on the same table to accomplish the same goal. On a large table (500K+ rows) this can be very expensive. This was a problem I ran into when generating real time report data on a recent project. So, I extended ActiveRecord with the following two methods to allow multiple SUM and SUM by CASE operations in a single query.

Now we achieved the same result with a single query. Make no mistake, this can save a lot of time. In the real time report generation case, execution time was cut from 70-98%! Reports that required 150+ queries could be condensed into 2 queries. This use case is on the extreme end of number crunching with Rails, so results may not be typical. However, even the simple example above, 12 round trips down to 1, will definitely save some time.

The above example demonstrates the select_sum_case() method. You specify each condition or case, as a hash and pass them as an array along with the attribute to be summed and an optional name for the sum. There is also a select_sum() statement which simply takes an attribute name to sum and an optional name for the summed value.