Aliasing calculation result, grouping, and summing in ActiveRecord query using Postgres

Question

I'm trying to calculate the total value of each OrderItem by multiplying the unit_price by the quantity and subtracting the line_discount, and then grouping by the product_id and summing the line_total so I can get the total line totals for each product.

I tried this first:

OrderItem.select("order_items.*, 
order_items.unit_price * order_items.quantity - order_items.line_discount AS line_total 
GROUP BY product_id").sum('line_total')

but I got this error:

PG::UndefinedColumn: ERROR:  column "line_total" does not exist
LINE 1: SELECT SUM(line_total) AS sum_line_total, product_id...
             ^
: SELECT SUM(line_total) AS sum_line_total, product_id AS product_id FROM "order_items" GROUP BY product_id
ActiveRecord::StatementInvalid: PG::UndefinedColumn: ERROR:  column "line_total" does not exist
LINE 1: SELECT SUM(line_total) AS sum_line_total, product_id...

I found a few questions on here where the answers suggested that wrapping the aliasing part of the query in a subquery was the solution, but I tried several variations on this and kept getting the same error.

Some of the variations with subqueries I tried:

OrderItem.select("order_items.*, 
(SELECT order_items.unit_price * order_items.quantity - order_items.line_discount AS line_total 
GROUP BY order_items.product_id)").sum('line_total')

OrderItem.select("order_items.*, 
(SELECT unit_price * quantity - line_discount AS line_total 
FROM order_items GROUP BY order_items.product_id)").sum('line_total')

OrderItem.select("order_items.*, 
(SELECT order_items.unit_price * order_items.quantity - order_items.line_discount AS line_total)")
.group(:product_id).sum("line_total")

How can I adjust this query to return the result I'm looking for?

Answer 1

If you're willing to give up selecting all the fields (which you probably don't want anyway, since you're just grouping by product_id), this query will do it for you:

OrderItem.group("product_id").sum("unit_price * quantity - line_discount")

The result will be a hash, where the keys are product_ids and the values are the sums.