• Status: Solved
• Priority: Medium
• Security: Public
• Views: 111

# SQL Server Divide columns and handle divide by zero

In SQL Server (2012), I want to divide the values of 2 columns. There are cases where there will be division by zero. I want to do the calculation and handle the division by zero gracefully. A simple example is AB / Hits to get Average. Some AB fields will be zero. Some sort of If statement? If AB = 0, then Average = ".000", else AB/Hits? Thanks for any help.
0
dodgerfan
1 Solution

Commented:
Two choices:
1. Use a where clause to exclude the rows with zero denominators.
2. Use a case statement to set the zero denominators to null.

Both demonstrated below...
``````declare @t1 table (id int,num1 int,num2 int)

insert into @t1
select 1,1,1 union all
select 2,3,2 union all
select 3,0,1 union all
select 4,1,0  union all
select 5,1,null union all
select 6,null,6

select id,num1,num2,num1/num2 as IntegerDivision,(0.0+num1)/num2 as DecimalDivision
from @t1 where num2<>0

select id,num1,num2
,cast(num1 as decimal)/case when num2=0 then null else num2 end as DecimalDivision
from @t1
``````
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.