Divide by zero error encountered even when divider is set as NOT NULL

Question

I've come across the error "Divide by zero error encountered" when running this query.

>    SUM(CASE WHEN EML_DateSent IS NOT NULL THEN 1 ELSE 0 END) AS [Sends],
        (SUM(CASE WHEN EML_DateViewed IS NOT NULL OR EML_DateClicked IS NOT NULL THEN 1 ELSE 0 END)) * 100 / SUM((CASE WHEN EML_Datesent IS NOT NULL THEN 1 ELSE 0 END)) AS [Views %],
        (SUM(CASE WHEN EML_DateClicked IS NOT NULL THEN 1 ELSE 0 END)) * 100 / SUM((CASE WHEN EML_DateViewed IS NOT NULL OR EML_DateClicked IS NOT NULL THEN 1 ELSE 0 END)) AS [Clicks %]

Its an edited existing stored procedure that now calculates percentages , any quick fix ?

You are having this error because it can be evaluated as zero! ` ELSE 0 END` on the two cases you are using. — Jorge Campos, Jul 16 '14 at 16:10
Its pretty clear that this fragment can suffer from a divide by zero — Hugh Jones, Jul 16 '14 at 16:11

score 0 · Answer 1 · answered Jul 16 '14 at 16:23

Try using a max/maxium statement depending on what provider you are using.

/ MAX(SUM((CASE WHEN EML_DateViewed IS NOT NULL OR EML_DateClicked IS NOT NULL THEN 1 ELSE 0 END)), 1)

This will use your sum if it has a value, if it is zero the division will use 1 instead.

score 0 · Answer 2 · answered Jul 17 '14 at 02:55

You don't show the grouping criteria but it's obvious at least one of the groups has one of the dates set to NULL over the entire group. First, you don't have to put all that logic in a sum function. The count function does that, counting all the not null values, ignoring the null values. Where that doesn't work is where you're checking both dates, but that is solved by a simple coalesce. You want a count of where one date or the other or both are not null. There you can play a little trick:

select count(EMS_DateSent) AS Sends,
    count(coalesce(EMS_DateViewed, EMS_DateClicked)) * 100
        / case count(EMS_Datesent) 
              when 0 then 1000000
              else count(EMS_Datesent)
          end as "Views %",
    count(EMS_DateClicked) * 100
        / case count(coalesce(EMS_DateViewed, EMS_DateClicked))
              when 0 then 1000000
              else count(coalesce(EMS_DateViewed, EMS_DateClicked))
          end AS "Clicks %"
from EML
group by whatever;

If the divisor is 0 (all nulls over the group), I have set to a large number so you get a very small answer. But this must be large relative to actual counts in your application so adjust as needed.

Divide by zero error encountered even when divider is set as NOT NULL

2 Answers2