SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
Register Now and get your question answered!
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Divide by zero - Sum with Case
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

1 Posts

Posted - 04/29/2013 :  18:55:46  Show Profile  Reply with Quote
I want to protect the following statement from a divide by zero error. if v.Loan_LoanAmt is 0 than just return 0. How can I achieve this?

select [MyColumn] = 
sum( case when v.Process_FundingDt between @begDt and @endDt and v.Loan_Purpose_Desc = 'PURCHASE' then v.Loan_LoanAmt else 0 end)
 / sum (case when v.Process_FundingDt between @begDt and @endDt then v.Loan_LoanAmt else 0 end)

Thanks for any help!


Flowing Fount of Yak Knowledge

4614 Posts

Posted - 04/29/2013 :  19:31:18  Show Profile  Reply with Quote
This is a basic pattern to handle that:

SELECT COALESCE(Numerator / NULLIF(Denominator, 0), 0)

You might be able to use just use a COALESCE and repalce "else 0" with "else NULL"

Edited by - Lamprey on 04/29/2013 19:32:34
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000