SQL 2017: divide by zero error encountered

Question

Hi Everyone - Below is my query which is erroring out. Need your help to fix this. Thanks

Select *, (AverageSpend/CASE WHEN @year = YEAR(GETDATE()) THEN MONTH(GETDATE()) ELSE 12 END )*12 as annual,
concat((AverageSpend/cast(AnnualCap as float)) * 100,'%' ) as AnnualCapSpentPercent,
concat((AverageDaystomeetcap/cast(NumberofPatientsMetCap as float)) * 100,'%' ) as PercentPatientsMetAnnualCap

	 from #DollarCapExecutive  

Answer 1

Hi @kkran ,

The reason for this error is that your divisor contains 0.According to the data you provided, the value of NumberofPatientsMetCap contains 0. Therefore, as DanGuzman said, the value of NumberofPatientsMetCap contains 0 which caused an error.For this problem, you can refer to the following three solutions：
Method 1: SQL NULLIF Function

    create table #DollarCapExecutive  
    (Prgld int,AverageSpend float,AnnualCap int,AverageDaystomeetcap int,NumberofPatientsMetCap int)  
    insert into  #DollarCapExecutive values(10112,108.35,2000,6,5),(10112,2071.90,35000,136,1),  
    (10112,805.62,35000,0,0),(10112,1059.44,15000,0,0),(10112,48.74,300,12,18)  
      
    Select --(AverageSpend/CASE WHEN year = YEAR(GETDATE()) THEN MONTH(GETDATE()) ELSE 12 END ) as annual,  
    concat((AverageSpend/cast(AnnualCap as float))* 100,'%' ) as AnnualCapSpentPercent,  
    concat((AverageDaystomeetcap/cast(nullif(NumberofPatientsMetCap,0) as float)) *100,'%' ) as PercentPatientsMetAnnualCap  
    from #DollarCapExecutive  
      
    drop table #DollarCapExecutive  

Output:

AnnualCapSpentPercent PercentPatientsMetAnnualCap  
5.4175% 120%  
5.91971% 13600%  
2.30177% %  
7.06293% %  
16.2467% 66.6667%    

Method 2: Using CASE statement to avoid divide by zero error

Select --(AverageSpend/CASE WHEN year = YEAR(GETDATE()) THEN MONTH(GETDATE()) ELSE 12 END ) as annual,  
concat((AverageSpend/cast(AnnualCap as float))*100,'%' ) as AnnualCapSpentPercent,  
case when NumberofPatientsMetCap=0 then null else  
concat((AverageDaystomeetcap/cast(NumberofPatientsMetCap as float))*100,'%') end as PercentPatientsMetAnnualCap  
from #DollarCapExecutive  

Output:

AnnualCapSpentPercent PercentPatientsMetAnnualCap  
5.4175% 120%  
5.91971% 13600%  
2.30177% NULL  
7.06293% NULL  
16.2467% 66.6667%  

Method 3: SET ARITHABORT OFF
For details, please refer to the following article.
This method may cause performance problems, if not necessary, it is best to choose the first two methods

Methods to avoid the SQL divide by zero error

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

It seems you have zero values for NumberOfPatientsMetCap so the percent calculation fails with the error. One way to avoid the error is with NULLIF(NumberOfPatientsMetCap ,0) to coerce a NULL value. The result after the CONCAT will be '%' with this method so you may also want to tweak the expression according to how you want to display it.