CODEWITHSUNDEEP
ruby-on-rails-3activerecordsumconditional-statements
dannymcc
dannymcc

Reputation: 3814

Rails 3 Active Record and sums

I have the following code in my Rails 3 application.

@clinical_healthplan_spend = Clinical.select("ClientKey as client_key, PVID as animal_key, sum(Payments) as total_payments")
                                 .where("ClinicalText LIKE 'Paid by PET HEALTH' AND PVID != 44444")
                                 .order("sum(Payments) DESC")
                                 .group("animal_key")

I am trying to achieve the following:

  1. Order the results by the highest total_payments first.
  2. Limit the table by records with a total_payment value of at least 1500.

For the first requirement;

.order("sum(Payments) DESC")

Doesn't work and nor does

.order("total_payments DESC")

For the second requirement;

.where("total_payments > 1500")

doesn't work.

Am I putting the conditions in the right place, or is there a different way of doing these when using the sum option?

Any help would be appreciated!

Upvotes: 1

Views: 825

Answers (1)

Mischa
Mischa

Reputation: 43298

Instead of where, you have to use having. And in your group you have to include all the fields that don't have an aggregate. In this case animal_key and client_key. Other than that I don't see any problems with your code. This should work:

Clinical.select('ClientKey as client_key, PVID as animal_key, sum(Payments) as total_payments')
  .where("ClinicalText LIKE 'Paid by PET HEALTH' AND PVID != 44444")
  .group('animal_key, client_key')
  .having('sum(Payments) > 1500')
  .order('sum(Payments) DESC')

Upvotes: 4

Related Questions