CODEWITHSUNDEEP
sqlpostgresqlrails-activerecord
abhishek77in
abhishek77in

Reputation: 1896

How do I get sum of a column with different where conditions on the same table in rails?

I have method which calculates total income from receipts table. But currently it makes two sql calls every time the method is called, is it possible to improve the sql to make only one database call. I am fine with raw sql if needed.

  def total
    Receipt.where(receipt_type: 'income').sum(:amount) - Receipt.where(receipt_type: 'refund').sum(:amount)
  end

Main objective is avoid two database calls. And if the difference can be calculated in the database only it will be even better. Thanks.

Upvotes: 0

Views: 417

Answers (1)

Ruben Helsloot
Ruben Helsloot

Reputation: 13139

In SQL, I would solve this with

SELECT SUM(amount) FILTER (WHERE receipt_type = 'income')
       - SUM(amount) FILTER (WHERE receipt_type = 'refund')
FROM receipt

but I wasn't able to find the equivalent in ruby. I have no experience with it, but from this example, I think the following should work:

Receipt.select("SUM(amount) FILTER (WHERE receipt_type = 'income') - SUM(amount) FILTER (WHERE receipt_type = 'refund') )

Upvotes: 1

Related Questions