CODEWITHSUNDEEP
sqlsql-server-2005
challengeAccepted
challengeAccepted

Reputation: 7600

Cannot be divided by zero in SQL Error

I am wondering if there is a way to not use the division if the divisor = 0. In this case, i have the query working fine except when it has to be divided by 0. It should give the answer as 0 if the divisor is 0.Is it possible? This is in SQl 2005.

Thanks!

Select sum(cast(isnull(S.AmountSold, 0) as numeric(10,2))) As DeliveryTotal,
sum(cast(isnull(S.S_AmountCollected, 0) as numeric(10,2))) as DeliveryTotalCollected
(sum(cast(isnull(S.S_AmountCollected, 0) as numeric(10,2))) / sum(cast(isnull(S.AmountSold, 0) as numeric(10,2))) )*100 as DeliveryTotalPercentageCollected

from Info RD

Upvotes: 0

Views: 151

Answers (2)

Raphaël Althaus
Raphaël Althaus

Reputation: 60493

I would use case when, and maybe a subquery to make things easier to read...

select DeliveryTotal, DeliveryTotalCollected, 
 case 
   when DeliveryTotalCollected = 0 --if divisor = 0
   then 0 --return 0
   else (DeliveryTotal / DeliveryTotalCollected) * 100 --return division * 100
 end as DeliveryTotalPercentageCollected
from
(Select 
 sum(cast(isnull(S.AmountSold, 0) as numeric(10,2))) As DeliveryTotal,
 sum(cast(isnull(S.S_AmountCollected, 0) as numeric(10,2))) as DeliveryTotalCollected
 from Info RD) as subq

Upvotes: 2

Luc Morin
Luc Morin

Reputation: 5380

Look into the CASE expression:

http://msdn.microsoft.com/en-us/library/ms181765.aspx

It will allow you to decide between the two cases

Upvotes: -1

Related Questions