When running a script I get "divide by zero” in SQL

Question

I am trying to run a script in SQL in which I am dividing but I am getting the error "Divide by zero error encountered"

I tried to add in NULLIF which was suggested in an old post I read but this didnt help it.

SELECT SUM([SE Gross]) AS [Donors Gross],
       ROUND((SUM([Cancelled]) * 100) / NULLIF(SUM([SE Gross]), 2), 0),
       SUM([Venue Cost]),
       SUM([Accom/Expenses]),
       ROUND(SUM([SE Gross]) / NULLIF(SUM([fundraising days]), 2), 0) AS Column1,
       ROUND(SUM([Venue Cost]) / NULLIF(SUM([SE Gross]), 2), 0) AS Column2,
       ROUND(SUM([Venue Cost] + [Accom/Expenses]) / NULLIF(SUM([SE Gross]), 2), 0) AS column3,
       ROUND(SUM([Venue Cost] + [Accom/Expenses]) / NULLIF(SUM([donor net COUNT]), 2), 0) AS column4
FROM dbo.TEST1;

The result I currently get is: Divide by zero error encountered.

I think you probably wanted ISNULL, not NULLIF. Did you actually go and read the documentation to understand what that function does?? Don't blindly copy code from the internet without understanding at least the basic idea of it. Since search engines are so powerful and documentation and examples are so plentiful these days, there's usually very little excuse for not doing at least a bit of simple research, it only takes a few seconds — ADyson
There is a division error, so (as a debug step) split the two components of that division into separate columns and inspect for 0's — Hans Keﬆing
Ps. If the data in your field is actually just 0, then ISNULL won't help you either....maybe a CASE statement might be better. You didn't show us any sample data, so we can't be sure precisely what situation is occurring — ADyson
Looks like you systematically swapped the arguments to ROUND and NULLIF. You're currently using NULLIF(SUM([SE Gross]), 2), which will turn the result into NULL if SUM([SE Gross]) is exactly equal to 2. You want NULLIF(SUM([SE Gross]), 0) (and round to 2 digits, presumably). — Jeroen Mostert
@ADyson I'm sure op did not want to use isnull. Do you understand what nullif is supposed to achieve here? There's no need to admonish op quite so aggressively (especially when you apparently don't understand what they are trying to achieve). — HoneyBadger

Stanislovas Kalašnikovas Stanislovas Kalašnikovas · Accepted Answer · 2019-05-24T11:29:05

You're trying to convert to NULL if it's equal to 2. Check this part: NULLIF(SUM([SE Gross]), 2), you have to change 2 to 0 in the following:

SELECT SUM([SE Gross]) AS [Donors Gross],
       ROUND((SUM([Cancelled]) * 100) / NULLIF(SUM([SE Gross]), 0), 0),
       SUM([Venue Cost]),
       SUM([Accom/Expenses]),
       ROUND(SUM([SE Gross]) / NULLIF(SUM([fundraising days]), 0), 0) AS Column1,
       ROUND(SUM([Venue Cost]) / NULLIF(SUM([SE Gross]), 0), 0) AS Column2,
       ROUND(SUM([Venue Cost] + [Accom/Expenses]) / NULLIF(SUM([SE Gross]), 0), 0) AS column3,
       ROUND(SUM([Venue Cost] + [Accom/Expenses]) / NULLIF(SUM([donor net COUNT]), 0), 0) AS column4
FROM dbo.TEST1;

Also, you could use CASE statement to avoid null of entire entry if divider = 0, in the following:

CASE WHEN NULLIF(SUM([SE Gross]), 0) = 0 THEN 1 ELSE NULLIF(SUM([SE Gross]), 0) END

Or combine ISNULL with NULLIF in the following:

ISNULL(NULLIF(SUM([SE Gross]), 0), 1)

My guess is that you wanted to round up to 2 decimal places so in this case, it should be like this:

ROUND((SUM([Cancelled]) * 100) / ISNULL(NULLIF(SUM([SE Gross]), 0), 1), 2)

When running a script I get "divide by zero” in SQL

2 Answers