SQL Server: How to handle divide by 0 cases in query

Question

Hi all,

I have a query that looks like this, and which is getting a percentage:

SELECT SUM(convert(decimal(3,2),
          CASE WHEN Status_ID = 1 AND State_ID = 14 THEN 1 ELSE 0 END))/
          SUM(convert(decimal(3,2),CASE WHEN State_ID = 14 THEN 1 ELSE 0 END)) * 100
FROM SomeTable

This may return something like 59.77803 in a normal case. But it turns that there are (rightly) cases when the second SUM -- the denominator -- could be 0. Does anyone know of a way to account for this case and avoid a divide by 0 exception?

I'm using SQL Server 2005. Thanks.

Answer 1

SELECT SUM(convert(decimal(3,2),
          CASE WHEN Status_ID = 1 AND State_ID = 14 THEN 1 ELSE 0 END))/
          SUM(convert(decimal(3,2),CASE WHEN State_ID = 14 THEN 1 ELSE 0 END)) + 0.00000000001 * 100
FROM SomeTable

Answer 2

ask yourself what's the result you want to return when the denominator is 0?
then based on that fix the query accordingly.

there's no good built-in way to do it. It's a business logic thing.

Answer 3

If I'm reading it right, this should work:

SELECT SUM(convert(decimal(3,2), 
          CASE WHEN Status_ID = 1 THEN 1 ELSE 0 END))/ 
          SUM(convert(decimal(3,2),1)) * 100 
FROM SomeTable 
WHERE State_ID = 14 

Answer 4

To avoid divide by 0 exception you can use a function which accepts numerator,denominator and default value to pass when denominator is 0. And call that function to do division.

CREATE FUNCTION [dbo].[fn_divide] 
(
    @numerator DECIMAL(3,2),@denominator DECIMAL(3,2),@default DECIMAL(3,2)
)  
RETURNS DECIMAL(3,2) 

AS  BEGIN

 DECLARE @result DECIMAL(3,2)

 IF @denominator = 0
 BEGIN
    set @result=@default
 END
ELSE
 BEGIN
    set @result=@numerator/@denominator
 END 

  return @result
END

Answer 5

The real question is what should the "answer" be when you try to divide by zero? I'd make it NULL, so try something like this:

DECLARE @x int

set @x=5
select 1.0/NULLIF(@x,0)

set @x=0
select 1.0/NULLIF(@x,0)

OUTPUT:

---------------------------------------
0.200000000000

(1 row(s) affected)


---------------------------------------
NULL

(1 row(s) affected)

your code would be:

SELECT SUM(convert(decimal(3,2), CASE
                                     WHEN Status_ID = 1 AND State_ID = 14 THEN 1 
                                     ELSE 0 
                                 END
                  )
          ) 
        /
        SUM(convert(decimal(3,2),CASE
                                     WHEN State_ID = 14 THEN 1 
                                     ELSE NULL   --<<<<<<<<<<<<force null if div by zero
                                 END
                   )
           )
        * 100
FROM SomeTable

I don't really understand the WHEN Status_ID = 1 AND State_ID = 14 THEN 1, but that is the OPs code.

Answer 6

I would probably use a NULL to represent uncalculatable, but it depends upon your intent in your problem domain (code expanded to show what's going on):

SELECT SUM(convert(decimal(3,2),
                   CASE WHEN Status_ID = 1 AND State_ID = 14 THEN 1 ELSE 0 END)
          )
       /
       NULLIF(
           SUM(convert(decimal(3,2),
                       CASE WHEN State_ID = 14 THEN 1 ELSE 0 END)
           )
           , 0
       )
       * 100
FROM SomeTable

The result when there are no rows in the table so the denominator would be zero - gets converted to NULL so the whole expression ends up NULL.

That code can also be simplified a little (probably because it's already a simplified toy problem):

SELECT SUM( convert(decimal(3,2), CASE WHEN Status_ID = 1 THEN 1 ELSE 0 END) )
       /
       NULLIF( convert(decimal(3,2), COUNT(*)), 0 )
       * 100
FROM SomeTable
WHERE State_ID = 14

As an example of combining a number of different things into one query:

SELECT CASE WHEN State_ID = 14 THEN 'State_ID = 14'
            WHEN State_ID IN (1, 2, 3) THEN 'State_ID IN (1, 2, 3)'
            ELSE 'DEFAULT'
       END AS Category
       ,SUM(convert(decimal(3,2),
                   CASE WHEN Status_ID = 1 THEN 1 ELSE 0 END)
          )
       /
       convert(decimal(3,2), COUNT(*))
       * 100
FROM SomeTable
GROUP BY CASE WHEN State_ID = 14 THEN 'State_ID = 14'
            WHEN State_ID IN (1, 2, 3) THEN 'State_ID IN (1, 2, 3)'
            ELSE 'DEFAULT'
       END