CODEWITHSUNDEEP
sql-servercasedivide-by-zero
J.S.Orris
J.S.Orris

Reputation: 4821

Avoiding Divide By 0 Error With Multiple Case Statements

The following is part of a SELECT clause that is giving me divide by 0 error:

(SUM([Hours])/(SUM(CASE WHEN J.Description <> 'I' THEN 1 ELSE 0 END))

The following is how I am rectifying the denominator for the divide by 0 error:

CASE WHEN(SUM(CASE WHEN J.Description <> 'I' THEN 1 ELSE 0 END)) = 0 THEN 1 ELSE SUM(CASE WHEN J.Description <> 'I' THEN 1 ELSE 0 END))  AS 'MTBUR'

This is leaving me syntactical errors. What am I doing wrong?

So basically if the SUM = 0 then I want the denominator to be 1 ELSE

SUM(CASE WHEN J.Description <> 'I' THEN 1 ELSE 0 END

Upvotes: 0

Views: 791

Answers (2)

J.S.Orris
J.S.Orris

Reputation: 4821

CASE WHEN(SUM(CASE WHEN J.Description <> 'I' THEN 1 ELSE 0 END)) = 0 THEN 1 ELSE SUM (CASE WHEN J.Description <> 'I' THEN 1 ELSE 0 END)) END AS 'MTBUR'

Needed another END to end nested case

Upvotes: 1

Sean Lange
Sean Lange

Reputation: 33581

How about this? Let the else portion of your case expression be NULL so the sum will be NULL if there are no rows where Description = 'I'

SUM([Hours]) /  ISNULL(SUM(CASE WHEN J.Description <> 'I' THEN 1 END), 1)

Upvotes: 1

Related Questions