CODEWITHSUNDEEP
sql
Nugeswale
Nugeswale

Reputation: 63

Avoiding a divide by zero error in a case statement

I am getting a divide by zero error in my script. Can anyone please help.

I am trying to divide two records and one of them has zero in it. I dont want to lose the row, please advise.

select DATEPART(Year,Request_date) as "Year",
       DATEPART(Month,Request_date) as "Month",
       COUNT([MONTH_OF_SUSPENSION]) as "Request" ,
       sum(case when [PAYMENT_<=24HRS] = 'Y' then 1 else 0 end) as "Paid in 24hrs",   
       COUNT([MONTH_OF_SUSPENSION])/sum(case when [PAYMENT_<=24HRS] = 'Y' then 1 else 0 end) as "Achieved"
FROM suspension_br
  where REQUEST_STATUS = 'OTHERS'
GROUP BY DATEPART(Year,Request_date),DATEPART(Month,Request_date)

Upvotes: 0

Views: 584

Answers (3)

Dimt
Dimt

Reputation: 2328

Looking at your code I could assume you are using MSSQL and therefore you could use nullif which returns null if two arguments are equal. So for example your code could look like :

COUNT([MONTH_OF_SUSPENSION])/nullif(sum(case when [PAYMENT_<=24HRS] = 'Y' then 1 else 0 end),0) as "Achieved"

What it does is if the value of the sum operator is equal 0 then the divisor is turn from zero into null and that will result in the entire equation to become null.

Upvotes: 1

StevieG
StevieG

Reputation: 8709

use another case statement to check the result of your sum 

select DATEPART(Year,Request_date) as "Year",
       DATEPART(Month,Request_date) as "Month",
       COUNT([MONTH_OF_SUSPENSION]) as "Request" ,
       sum(case 
             when [PAYMENT_<=24HRS] = 'Y' then 1 
             else 0 
           end) as "Paid in 24hrs",   
       case 
         when sum(case 
                    when [PAYMENT_<=24HRS] = 'Y' then 1 
                    else 0 
                  end) = 0 then 'whatever you want in this case' 
         else COUNT([MONTH_OF_SUSPENSION])/sum(case 
                                                 when [PAYMENT_<=24HRS] = 'Y' then 1  
                                                 else 0 
                                               end) as "Achieved"
FROM suspension_br
  where REQUEST_STATUS = 'OTHERS'
GROUP BY DATEPART(Year,Request_date),DATEPART(Month,Request_date)

although this is pretty nasty looking, so you could tidy it up a bit with a sub-select:

select 
  year, 
  month, 
  request, 
  PaidIn24hrs, 
  case
    when PaidIn24hrs = 0 then 'whatever you want in this case'
    else request/PaidIn24hrs
  end as "Achieved"
from 
(
    select DATEPART(Year,Request_date) as "Year",
           DATEPART(Month,Request_date) as "Month",
           COUNT([MONTH_OF_SUSPENSION]) as "Request" ,
           sum(case 
                 when [PAYMENT_<=24HRS] = 'Y' then 1 
                 else 0 
               end) as "PaidIn24hrs"
    FROM suspension_br
      where REQUEST_STATUS = 'OTHERS'
    GROUP BY DATEPART(Year,Request_date),DATEPART(Month,Request_date)
)

Upvotes: 0

Patrick Hofman
Patrick Hofman

Reputation: 156998

You can introduce a second case to check the result of the sum:

case
when sum(case when [PAYMENT_<=24HRS] = 'Y' then 1 else 0 end) > 0
then COUNT([MONTH_OF_SUSPENSION])/sum(case when [PAYMENT_<=24HRS] = 'Y' then 1 else 0 end)
else 0 /* a default value that makes sense to you */
end as "Achieved"

Upvotes: 1

Related Questions