CODEWITHSUNDEEP
sql-server-2008
Nitin
Nitin

Reputation: 65

Sql Server Query Divide by zero error encountered

I ma getting error in my query. how i can resolve it. i have tried null if but it is not working.

Divide by zero error encountered 

SELECT ( 100 - ( ( sum(t1.cAmount) - ( SUM(t1.cAmount) * t2.rAdvancedAmotizationPercent / 100 ) ) * 100 ) / t3.rAmount ) AS restofTotalMoblization,
       ( ( sum(t1.cAmount) - ( SUM(t1.cAmount) * t2.rAdvancedAmotizationPercent / 100 ) ) * 100 ) / t3.rAmount           AS totalMoblization
FROM   tblPkgAdvances t1
       INNER JOIN tblFianacialInvoice t2
         ON t1.vsSysPackageId = 'GWSSP/WS/03'
       INNER JOIN tblPkgContractAwardDetails t3
         ON t2.iPackageId = t3.iPackageId
WHERE  t1.vsSysPackageId = 'GWSSP/WS/03'
GROUP  BY t1.cAmount,
          t2.rAdvancedAmotizationPercent,
          t3.rAmount

Upvotes: 0

Views: 414

Answers (2)

Pawan
Pawan

Reputation: 1075

you can try

SELECT ( 100 - ( ( sum(t1.cAmount) - ( SUM(t1.cAmount) * t2.rAdvancedAmotizationPercent / 100 ) ) * 100 ) / t3.rAmount ) AS restofTotalMoblization,
       ( ( sum(t1.cAmount) - ( SUM(t1.cAmount) * t2.rAdvancedAmotizationPercent / 100 ) ) * 100 ) / t3.rAmount           AS totalMoblization
FROM   tblPkgAdvances t1
       INNER JOIN tblFianacialInvoice t2
         ON t1.vsSysPackageId = 'GWSSP/WS/03'
       INNER JOIN tblPkgContractAwardDetails t3
         ON t2.iPackageId = t3.iPackageId
WHERE  t1.vsSysPackageId = 'GWSSP/WS/03' and t3.rAmount>0
GROUP  BY t1.cAmount,
          t2.rAdvancedAmotizationPercent,
          t3.rAmount

I have just filtered out records which are greater than 0

Upvotes: 0

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171451

Instead of 

(100 - ((sum(t1.cAmount) - (SUM(t1.cAmount) * t2.rAdvancedAmotizationPercent / 100)) * 100) / t3.rAmount)

you can do

case when t3.rAmount = 0 
then 0 
else (100 - ((sum(t1.cAmount) - (SUM(t1.cAmount) * t2.rAdvancedAmotizationPercent / 100)) * 100) / t3.rAmount) end

Upvotes: 1

Related Questions