CODEWITHSUNDEEP
sql-servert-sql
Rick Walker
Rick Walker

Reputation: 97

SQL - SUM-CASE-Divide by zero error

I want to protect the following statement from a divide by zero error. 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
 )

Upvotes: 2

Views: 1671

Answers (2)

Henrik Staun Poulsen
Henrik Staun Poulsen

Reputation: 13904

you could use the NULLIF method mentioned here: how to avoid divide by zero error

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
 ) / NULLIF(sum (
   case when v.Process_FundingDt 
   between @begDt and @endDt 
   then v.Loan_LoanAmt else 0 
   end, 0)
 )

Upvotes: 0

HABO
HABO

Reputation: 15852

This may help:

select [MyColumn] = case when Denominator = 0 then NULL else Numerator / Denominator end
  from (
   select sum( 
   case when v.Process_FundingDt between @begDt and @endDt and v.Loan_Purpose_Desc = 'PURCHASE' then v.Loan_LoanAmt 
     else 0 end ) as Numerator,
   sum (
   case when v.Process_FundingDt between @begDt and @endDt then v.Loan_LoanAmt
     else 0 end ) as Denominator
   ) as Edgar

It probably works better with your missing FROM clause added.

Of course, it may run afoul of this feature, depending on what the query optimizer does.

Upvotes: 2

Related Questions