CODEWITHSUNDEEP
sql-server
user2201214
user2201214

Reputation: 57

sql server Divide by zero error

Any way I have a query in the following form

select 
(select count(*) from sometable where somecritiea) / ((select count(*) from sometable where somecritiea) + 0.000001) 
from sometable

Im dividing the result of a count by the result of a count, so nulls are not an issue.

When I run this I get a "Divide by zero error encountered.". Notice the +0.000001 in the divisor.

If I insert the enumerator (?) and divisor in a new table and then do the same calc it works as expected.

select 
(select count(*) from sometable where somecritiea) a/ ((select count(*) from sometable where somecritiea) + 0.000001) b
into testtable
from sometable

select a/b from testtable

this returns the expected results with no errors.

If I run 

select * from testtable where b = 0
i get 0 records as expected.

I really losing it here, this is a 5 minute job that has turn into a 5 hour mess.

I've also tried

select *
((select count(*) from sometable where somecritiea) + 0.000001) divisor
from sometable
where divisor = 0

This returns no records.

Upvotes: 2

Views: 657

Answers (3)

Kanwal Sarwara
Kanwal Sarwara

Reputation: 413

Try NULLIF on your divisor

select 
(select count(*) from sometable where somecritiea) / NULLIF((select count(*) from sometable     where somecritiea) + 0.000001), 0) from sometable

This will return NULL instead of divide by 0 whenever denominator is 0. If you dont want NULL but some other default answer(eg 9), use ISNULL on whole query to convert it back to 0.

Upvotes: 0

Pieter Geerkens
Pieter Geerkens

Reputation: 11893

This is the closest I could come to replicating your scenario quickly, and it works fine; returns 0.0000000:

with sometable as (
  select * from ( values 
    (0)
  ) sometable(value)
)
select 
  (select count(*) from sometable where value <> 0 )
/ 
( (select count(*) from sometable where value <> 0 ) + 0.000001 ) 
from sometable

Upvotes: 1

Kenneth Fisher
Kenneth Fisher

Reputation: 3812

Honestly I couldn't reproduce your problem either but this should work regardless.

WITH Divisor AS (select count(*)*1.0 BotCnt from sys.procedures),
    Dividend AS (select count(*)*1.0 TopCnt from sys.tables )
SELECT 
    CASE WHEN BotCnt = 0 THEN 0 ELSE TopCnt/BotCnt END,
    *
FROM sys.objects
CROSS JOIN Divisor
CROSS JOIN Dividend

Note the *1.0. That is to convert the count to a decimal. You could use an explicit conversion just as easily. The CROSS JOINs only works because the two CTEs are single row. Otherwise use an INNER JOIN with an ON clause.

Upvotes: 3

Related Questions