Rails: prevent division by zero with postgres

I have my rails app with a postgre database and I'm trying to get some indicators, with the following statement

Negotiation.find(:all, :conditions => conditions_hash('negotiation'), :select => 'sum(payment_term * amount_after_annualised_base) / sum(amount_after_annualised_base) as pt').first

how can I make sure that sum(amount_after_annualised_base) is not 0 (thus doesn't throw a "division by zero" error)?

I've found this, but not sure it is correct / the best / most efficient way.

Negotiation.find(:all开发者_StackOverflow社区, :conditions => conditions_hash('negotiation'), :select => 'sum(payment_term * amount_after_annualised_base) / (CASE sum(amount_after_annualised_base) WHEN 0 THEN NULL ELSE sum(amount_after_annualised_base) END) as pt').first

thanks! Pierre

use nullif:

Negotiation.find(:all, :conditions => conditions_hash('negotiation'), :select => 'sum(payment_term * amount_after_annualised_base) / nullif(sum(amount_after_annualised_base),0) as pt').first

which is just a consise shorthand for your solution:

Negotiation.find(:all, :conditions => conditions_hash('negotiation'), :select => 'sum(payment_term * amount_after_annualised_base) / (CASE sum(amount_after_annualised_base) WHEN 0 THEN NULL ELSE sum(amount_after_annualised_base) END) as pt').first

How about:

Negotiation.find(:all, 
                 :conditions => conditions_hash('negotiation'), 
                 :select => 'sum(amount_after_annualised_base) sum_amount,
                             sum(payment_term * amount_after_annualised_base) / greatest(1,sum_amount) as pt').first

and check in your code if sum_amount is zero.

I think you would need to write something like

Negotiation.find(
  :all, :conditions => conditions_hash('negotiation'), 
  :select => 'CASE sum(amount_after_annualised_base) WHEN 0 THEN NULL ELSE sum(payment_term * amount_after_annualised_base) / sum(amount_after_annualised_base) END as pt').first

which would return NULL if sum(amount_after_annualised_base) is zero, and else does the requested division.