Divide By Zero Error Encountered In T Sql
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring t sql nullif developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question divide by zero error encountered in sql server x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; divide by zero error encountered in sql server 2012 it only takes a minute: Sign up Simple way to prevent a Divide By Zero error in SQL up vote 9 down vote favorite 2 I have a SQL query which used to cause a Divide By Zero exception, I've divide by zero error encountered sql server 2008 wrapped it in a CASE statement to stop this from happening. Is there a simpler way of doing this? Here's my code: Percentage = CASE WHEN AttTotal <> 0 THEN (ClubTotal/AttTotal) * 100 ELSE 0 END sql sql-server-2008 tsql sql-server-2005 share|improve this question asked Oct 28 '13 at 9:22 Denys Wessels 10.5k43872 1 It depends on how you wan't your query and result to behave when AttTotal is 0 or NULL, so there is no general way of
Tsql Divide By Zero Error Encountered
doing it. I'd say your query is just fine. Even if you write your query differently using ISNULL or NULLIF at the end it will likely execute the same. –Nenad Zivkovic Oct 28 '13 at 9:34 As has been pointed out by others, the logic in this CASE statement doesn't quite make sense to us. See my comment on my answer for a brief explanation and then choose whether you want your original answer or mine. My answer would be a more usual solution. –Tom Chantler Oct 28 '13 at 9:40 1 Possible duplicate of How to avoid the "divide by zero" error in SQL? –Henrik Staun Poulsen Mar 10 at 21:06 add a comment| 5 Answers 5 active oldest votes up vote 26 down vote accepted A nicer way of doing this is to use NULLIF like this: Percentage = 100 * ClubTotal / NULLIF(AttTotal, 0) share|improve this answer answered Oct 28 '13 at 9:25 Tom Chantler 11.2k42940 2 This will return NULL if AttTotal = 0, while the query in question returns 0. –Nenad Zivkovic Oct 28 '13 at 9:31 3 @NenadZivkovic - Returning NULL for a divide by zero error probably makes more mathematical sense anyway though. –Martin Smith Oct 28 '13 at 9:34 I agree with @Martin Smith. If you have a situation where the ClubTotal is a number and AttTotal is zero, it doesn't make
3001-4000 Messages 4001-5000 Messages 5001-6000 Messages 6001-7000 Messages 7001-7500 Messages 7501-8000 Messages 8001-8500 Messages 8501-9000 Messages 9001-9500 Messages 9501-10000 Messages 10001-10500 Messages 10501-11000
Divide By Zero Error Encountered Excel
Messages 11001-11500 Messages 11501-12000 Messages 12001-13000 Messages 13001-13500 Messages 14001-14500 Home>SQL msg 8134 level 16 state 1 line 1 divide by zero error encountered Server Error Messages> Msg 8134 - Divide by zero error encountered SQL Server Error Messages - Msg 8134 oracle sql divide by zero - Divide by zero error encountered SQL Server Error Messages - Msg 8134 Error Message Server: Msg 8134, Level 16, State 1, Line 1 Divide by zero error encountered. http://stackoverflow.com/questions/19631017/simple-way-to-prevent-a-divide-by-zero-error-in-sql Causes: This error is caused by performing a division operation wherein the denominator or the divisor is 0. This error is not encountered when the denominator or divisor is NULL because this will result to a NULL value. Solution / Work Around: There are three ways to avoid the "Division by zero encountered" error in your SELECT statement and http://www.sql-server-helper.com/error-messages/msg-8134.aspx these are as follows: CASE statement NULLIF/ISNULL functions SET ARITHABORT OFF and SET ANSI_WARNINGS OFF Using the CASE statement, your query will look like the following: SELECT CASE WHEN [Denominator] = 0 THEN 0 ELSE [Numerator] / [Denominator] END AS [Percentage] FROM [Table1] If the denominator or divisor is 0, the result becomes 0. Otherwise, the division operation is performed. Using the NULLIF and ISNULL functions, your query will look like the following: SELECT ISNULL([Numerator] / NULLIF([Denominator], 0), 0) AS [Percentage] FROM [Table1] What this does is change the denominator into NULL if it is zero. Then in the division, any number divided by NULL results into NULL. So if the denominator is 0, then the result of the division will be NULL. Then to return a value of 0 instead of a NULL value, the ISNULL function is used. Lastly, using the SET ARITHABORT and SET ANSI_WARNINGS, your query will look like the following: SET ARITHABORT OFF SET ANSI_WARNINGS OFF SELECT [Numerator] / [Denominator] With both ARITHABORT and ANSI_WARNINGS set to OFF, SQL
Using NULLIF() To Prevent Divide-By-Zero Errors In SQL By Ben Nadel on October 3, 2007 Tags: SQL Boyan Kostadinov just sent me a cool link to an article that is the final part in a four https://www.bennadel.com/blog/984-using-nullif-to-prevent-divide-by-zero-errors-in-sql.htm part series that discusses the SQL NULL value. I haven't read the first three parts yet, but there is a really cool tip in the fourth part on using NULLIF() to prevent divide-by-zero errors in http://sqlserverplanet.com/tsql/overcome-divide-by-zero-using-nullif a SQL call.The idea here is that, as with any other form of math that I know of, you cannot divide by zero in a SQL call. Therefore, running this code: by Zero Using NULLIF Posted on April 5, 2012 by Derek Dieter 1 4 « A Query Method Faster Than BETWEENCreating Unique Random Numbers » Anytime we are dividing we need to think of the divide by zero scenario. Even if you think you will never encounter this with your result set, it's advisable to guard against it because when divide by zero is encountered, an error is thrown. The best method I've found to overcome this is by using the NULLIF function. This function takes two parameters and if they are equal, a NULL value is returned. Lets take a look at an example that throws a divide by zero error. DECLARE @iter FLOAT; DECLARE @num FLOAT SET @num = 10; SET @iter = 5; WHILE @iter > -5 BEGIN SELECT @num / @iter SET @iter = @iter - 1 END Running the following query, we see that once the variable @iter becomes zero, we receive an error. So the most elegant way to overcome this is to use NULLIF function and compare @iter to zero. When it does equal zero, it will instead change it to a null. And when dividing anything by NULL will equal a NULL. DECLARE @iter FLOAT; DECLARE @num FLOAT; SET @num = 10; SET @iter = 5; WHILE @iter > -5 BEGIN SELECT @num / NULLIF(@iter,0); SET @iter = @iter - 1; END This executes without error, however we still receive a null as a result. If you need otherwise, then you may want to wrap the equation in an ISNULL, to return a different value. DECLARE @iter FLOAT; DECLARE @num FLOAT; SET @num = 10; SET @iter = 5; WHILE @iter > -5 BEGIN SELECT ISNULL(@num / NULLIF(@iter,0),@num); SET @iter = @iter - 1; END This will just return the same number you are dividing by, if you encounter a NULL denominator. Filed under TSQL Related Posts: SQL Server For Each Row Next SQL Server Begin Try Concatenate Rows Using Coalesce Alter Index All Tables How to Concatenate SQL Rank SQL Replace Compare Stored Procedure Output by Creating a Test Harness Substring Between Two Characters Post a comment Comments (RSS) Trackback Permalink Click here to cancel reply. Name: Email: Comment: One comment Jiwa 29 Aug 2015 at 8:36 am ( 2012.02.11 08:02 ) : This feeder works great as long as you fololw the directions and complete ALL the steps for setting it up. After programming the current time, you set the times you want them to eat, THEN go back to those times and select 1