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

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

SELECT SUM(convert(decimal(3,2),
          CAS开发者_开发问答E 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.

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

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.

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 

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

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

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.