Division by zero ошибка sql

Обновлено: 18.05.2024

Msg 8134, Уровень 16, состояние 1, Линия 1 делится на нулевую ошибку.

Я мог бы сделать одно из следующих действий:

добавьте предложение where, чтобы мой делитель никогда не был равен нулю

я мог бы добавить оператор case, так что есть особое отношение к нулю.

- лучший способ использовать NULLIF предложения?

есть ли лучший способ, или как это может быть реализовано?

чтобы избежать ошибки "деление на ноль", мы запрограммировали ее следующим образом:

но вот гораздо более приятный способ сделать это:

теперь единственная проблема-запомнить бит NullIf, если я использую ключ"/".

Если вы хотите вернуть ноль, в случае, если произойдет нулевое деление, вы можете использовать:

для каждого делителя, который равен нулю, вы получите ноль в результирующем наборе.

19 Answers 19

In order to avoid a "Division by zero" error we have programmed it like this:

But here is a much nicer way of doing it:

Now the only problem is to remember the NullIf bit, if I use the "/" key.

12k 4 4 gold badges 22 22 silver badges 22 22 bronze badges A much nicer Way of doing it "Select dividend / nullif(divisor, 0) . " breaks if divisor is NULL. @ErikE, it is true. try running . select 1/nullif(null,0) . you get "The type of the first argument to NULLIF cannot be the NULL constant because the type of the first argument has to be known." Handle this by using "coalesce(FieldName,0)" . e.g. select 1/nullif(coalesce(null,0),0) @JohnJoseph I can’t tell if you’re agreeing with me or arguing with me.

In case you want to return zero, in case a zero devision would happen, you can use:

For every divisor that is zero, you will get a zero in the result set.

28.9k 21 21 gold badges 96 96 silver badges 123 123 bronze badges 2,856 1 1 gold badge 15 15 silver badges 12 12 bronze badges Some benchmarks reveal that COALESCE is slightly slower than ISNULL. However, COALESCE is in the standards so is more portable. @SQLGeorge While I agree with your argument, please note that there are cases that one cares more what is statistically correct than mathematically correct. In some cases when using statistics functions, 0 or even 1 is an acceptable result when divisor is zero. I think that @George and @James/ Wilson fundamentally misunderstand the question being asked. There are certainly business applications where returning a "0" is appropriate, even if it is not technically true from a mathematical point of view.

This seemed to be the best fix for my situation when trying to address dividing by zero, which does happen in my data.

Suppose you want to calculate the male–female ratios for various school clubs, but you discover that the following query fails and issues a divide-by-zero error when it tries to calculate ratio for the Lord of the Rings Club, which has no women:

You can use the function NULLIF to avoid division by zero. NULLIF compares two expressions and returns null if they are equal or the first expression otherwise.

Rewrite the query as:

Any number divided by NULL gives NULL , and no error is generated.

4,691 7 7 gold badges 44 44 silver badges 78 78 bronze badges Yes indeed, that is WAY BETTER than that other answer which has got so many upvotes. In your solution, you have at least a NULL, which indicates that you cannot provide a correct result. But if you convert the result from NULL to Zero, then you simply get wrong and misleading results.

You can also do this at the beginning of the query:

So if you have something like 100/0 it will return NULL. I've only done this for simple queries, so I don't know how it will affect longer/complex ones.

20.6k 5 5 gold badges 39 39 silver badges 59 59 bronze badges This "feels" so dirty but I love it! Needed it in a query that does aggregation and use CASE statement was not an option because then I had to add that column to the GROUP BY which totally changed the results. Making the initial query a subselect and then doing a GROUP BY on the outer query also changes the results because there is division involved. OK, so I still like this "solution" but like many of you probably felt, I felt there had to be a "cleaner" way. What if I forgot to re-enable the warnings? Or someone cloaned my code (that never happens, right?) and did not think about the warnings? Anyway, saw other answers about NULLIF(). I knew about NULLIF() but did not realize dividing by NULL returns NULL (I thought it would be an error). So. I went with the following: ISNULL( (SUM(foo) / NULLIF(SUM(bar),0) ), 0) AS Avg

You can at least stop the query from breaking with an error and return NULL if there is a division by zero:

However, I would NEVER convert this to Zero with coalesce like it is shown in that other answer which got many upvotes. This is completely wrong in a mathematical sense, and it is even dangerous as your application will likely return wrong and misleading results.

3,854 1 1 gold badge 19 19 silver badges 49 49 bronze badges

EDIT: I'm getting a lot of downvotes on this recently. so I thought I'd just add a note that this answer was written before the question underwent it's most recent edit, where returning null was highlighted as an option. which seems very acceptable. Some of my answer was addressed to concerns like that of Edwardo, in the comments, who seemed to be advocating returning a 0. This is the case I was railing against.

ANSWER: I think there's an underlying issue here, which is that division by 0 is not legal. It's an indication that something is fundementally wrong. If you're dividing by zero, you're trying to do something that doesn't make sense mathematically, so no numeric answer you can get will be valid. (Use of null in this case is reasonable, as it is not a value that will be used in later mathematical calculations).

So Edwardo asks in the comments "what if the user puts in a 0?", and he advocates that it should be okay to get a 0 in return. If the user puts zero in the amount, and you want 0 returned when they do that, then you should put in code at the business rules level to catch that value and return 0. not have some special case where division by 0 = 0.

That's a subtle difference, but it's important. because the next time someone calls your function and expects it to do the right thing, and it does something funky that isn't mathematically correct, but just handles the particular edge case it's got a good chance of biting someone later. You're not really dividing by 0. you're just returning an bad answer to a bad question.

Imagine I'm coding something, and I screw it up. I should be reading in a radiation measurement scaling value, but in a strange edge case I didn't anticipate, I read in 0. I then drop my value into your function. you return me a 0! Hurray, no radiation! Except it's really there and it's just that I was passing in a bad value. but I have no idea. I want division to throw the error because it's the flag that something is wrong.

Msg 8134, Level 16, State 1, Line 1 Divide by zero error encountered.

What is the best way to write SQL code so that I will never see this error message again?

I could do either of the following:

Add a where clause so that my divisor is never zero

I could add a case statement, so that there is a special treatment for zero.

Is the best way to use a NULLIF clause?

Is there better way, or how can this be enforced?

5,523 5 5 gold badges 29 29 silver badges 43 43 bronze badges 12k 4 4 gold badges 22 22 silver badges 22 22 bronze badges

This seemed to be the best fix for my situation when trying to address dividing by zero, which does happen in my data.

You can use the function NULLIF to avoid division by zero. NULLIF compares two expressions and returns null if they are equal or the first expression otherwise.

Rewrite the query as:

Any number divided by NULL gives NULL , and no error is generated.

You can also do this at the beginning of the query:

So if you have something like 100/0 it will return NULL. I've only done this for simple queries, so I don't know how it will affect longer/complex ones.

You can at least stop the query from breaking with an error and return NULL if there is a division by zero:

3,854 1 1 gold badge 19 19 silver badges 49 49 bronze badges

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

2 Answers

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

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

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

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

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.

это казалось лучшим решением для моей ситуации при попытке решить деление на ноль, что происходит в моих данных.

Предположим, вы хотите рассчитать соотношение мужчин и женщин для различных школьных клубов, но вы обнаружите, что следующий запрос терпит неудачу и выдает ошибку деления на ноль, когда он пытается рассчитать соотношение для клуба "Властелин колец", в котором нет женщин:

вы можете использовать функцию NULLIF чтобы избежать деления на ноль. NULLIF сравнивает два выражения и возвращает нуль, если они равны или первое выражение в противном случае.

переписать запрос так:

любое число, разделенное на NULL дает NULL , и ошибка не генерируется.

вы также можете сделать это в начале запрос:

Так что если у вас есть что-то вроде 100/0 он вернет значение NULL. Я сделал это только для простых запросов, поэтому я не знаю, как это повлияет на более длинные/сложные.

ответ: Я думаю, что здесь есть основная проблема, то есть деление на 0 не является законным. Это признак того, что что-то фундаментально неправильно. Если вы делите на ноль, вы пытаетесь сделать что-то, что не имеет смысла математически, поэтому никакой числовой ответ, который вы можете получить, не будет действительным. (Использование null в этом случае разумно, так как это не значение, которое будет использоваться в последующих математических расчетах).

поэтому Эдвардо спрашивает в комментариях: "что, если пользователь ставит 0?", и он выступает за то, чтобы получить 0 в ответ. Если пользователь помещает ноль в сумму, и вы хотите, чтобы 0 возвращалось, когда они это делают, то вы должны ввести код на уровне бизнес-правил, чтобы поймать это значение и вернуть 0. не имеют особого случая, когда деление на 0 = 0.

Это тонкая разница, но это важно. потому что в следующий раз, когда кто-то вызывает вашу функцию и ожидает, что она сделает правильную вещь, и она делает что-то фанки, что не является математически правильным, а просто обрабатывает конкретный случай edge у него есть хороший шанс укусить кого-нибудь позже. На самом деле вы не делите на 0. ты просто возвращаешь плохой ответ на плохой вопрос.

представьте, что я что-то кодирую, и я все испортил. Я должен был бы читать в значении масштабирования измерения излучения, но в странном случае края я не ожидал, я читал в 0. Затем я опускаю свою ценность в вашу функцию. ты возвращаешь мне ноль! Ура, никакой радиации! За исключением того, что это действительно там, и это просто то, что я передавал плохую ценность. но я понятия не иметь. Я хочу, чтобы подразделение выбросило ошибку, потому что это флаг, что что-то не так.

вы можете, по крайней мере, остановить запрос от разрыва с ошибкой и вернуть NULL если есть деление на ноль:

, Я никогда преобразовать это в ноль с coalesce как показано в другой ответ, который получил много положительных отзывов. Это совершенно неправильно в математическом смысле, и это даже опасно, поскольку ваше приложение, вероятно, вернет неправильные и вводящие в заблуждение результаты.

поймав ноль с nullif (), то результирующий null с isnull () вы можете обойти свое деление на нулевую ошибку.

замена "деления на ноль" на ноль является спорной, но это также не единственный вариант. В некоторых случаях замена на 1 (разумно) уместна. Я часто нахожу себя с помощью

когда я смотрю на сдвиги в баллах / подсчетах и хочу по умолчанию 1, Если у меня нет данных. Например

чаще всего я фактически рассчитал это соотношение где-то еще (не в последнюю очередь потому, что он может бросить некоторые очень большие коэффициенты регулировки для низких знаменатели. В этом случае я обычно контролирую для OldSampleScore больше порога; который затем исключает ноль. Но иногда "взлом" уместен.