Division Error In 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 developers or posting ads with us Stack divide by zero error sql Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow divide by zero error sql server is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up How to avoid the divide by 0 error sql “divide by zero” error in SQL? up vote 188 down vote favorite 46 I have this error message: 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 how to avoid divide by zero error in sql 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 Or 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? sql sql-server sql-server-2005 sql-server-2008 share|improve this question edited Jan 6 at 19:50 Hooper 4241525 asked May 14 '09 at 6:06 Henrik Staun Poulsen 4,89331220 4
How To Handle Divide By Zero Error In Sql
Perhaps some data validation is in order. –Anthony May 14 '09 at 19:17 add a comment| 15 Answers 15 active oldest votes up vote 350 down vote accepted In order to avoid a "Division by zero" error we have programmed it like this: Select Case when divisor=0 then null Else dividend / divisor End ,,, But here is a much nicer way of doing it: Select dividend / nullif(divisor, 0) ... Now the only problem is to remember the NullIf bit, if I use the "/" key. share|improve this answer edited Dec 20 '12 at 1:04 Community♦ 11 answered May 14 '09 at 6:10 Henrik Staun Poulsen 4,89331220 that's the way I would have solved it. –J. Polfer May 14 '09 at 19:21 4 A much nicer Way of doing it "Select dividend / nullif(divisor, 0) ..." breaks if divisor is NULL. –Anderson Dec 1 '14 at 10:51 add a comment| up vote 87 down vote In case you want to return zero, in case a zero devision would happen, you can use: SELECT COALESCE(dividend / NULLIF(divisor,0), 0) FROM sometable For every divisor that is zero, you will get a zero in the result set. share|improve this answer edited Jan 15 '13 at 19:41 Peter Mortensen 10.2k1369107 answered Jan 4 '12 at 12:06 Tobias Domhan 1,4431011 8 Some benchmarks reveal that COALESCE is slightly slower than ISNULL. However, COALESCE is in the standards so is more portable. –Paul Chernoch Jul 12 '12 at 14:29 16 If someone else doesn't instantly get why t
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
How To Fix Divide By Zero Error In Sql
Us Learn more about Stack Overflow the company Business Learn more about hiring developers how to solve divide by zero error in sql or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack oracle sql divide by zero Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Simple way to prevent a Divide By http://stackoverflow.com/questions/861778/how-to-avoid-the-divide-by-zero-error-in-sql 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 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 http://stackoverflow.com/questions/19631017/simple-way-to-prevent-a-divide-by-zero-error-in-sql 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 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:2
8 knows that division by zero is a no-no, or at least they should. Everybody who's used SQL for any amount of time knows that we can use a CASE expression to prevent division by zero. But do you know the other way http://www.itjungle.com/fhg/fhg051210-story02.html to prevent division by zero in SQL? Assume a database file called SomeTable, with fields called Quantity and Amount. Let's divide Amount by Quantity. First, here's the usual case statement. select itnbr, Amount, Quantity, case when Quantity <> 0 then Amount http://dba.stackexchange.com/questions/125363/avoiding-a-divide-by-zero-error-in-a-computed-column / Quantity else 0 end from SomeTable If Quantity is not zero, the division takes place. If Quantity is zero, the calculation yields a zero. We could just as easily return any other numeric value, the value one, for example. select divide by itnbr, Amount, Quantity, case when Quantity <> 0 then Amount / Quantity else 1 end from SomeTable The other way to prevent division by zero is to use the NULLIF function. NULLIF requires two arguments. If the arguments are equal, NULLIF returns a null value. If they are not equal, NULLIF returns the first value. Here's the SQL. select itnbr, Amount, Quantity, Amount / nullif(Quantity,0) from SomeTable If Quantity is not zero, the two arguments do not match, and the resulting calculation divide by zero is Amount divided by Quantity. But if Quantity is zero, the arguments do match, and the calculation is Amount divided by null, which yields a null value. If you use this technique, be sure to put the zero in the second argument of NULLIF. Post this story to del.icio.us Post this story to Digg Post this story to Slashdot Sponsored By SEQUEL SOFTWARE SEQUEL ViewPoint--Data Access & Analysisfor Power Systems Servers Easy to use by IT and end users Real-time data access and analysis Queries, reports, pivot tables, drill-down analysis, kpi dashboards IBM i-centric with multiplatform data access Expert support and training Secure data access GUI, Web & green screen interface SEQUEL--Fast, efficient & cost-effective data analysis Click here for a FREE Information Kit! Senior Technical Editor: Ted Holt Technical Editor: Joe Hertvik Contributing Technical Editors: Erwin Earley, Brian Kelly, Michael Sansoterra Publisher and Advertising Director: Jenny Thomas Advertising Sales Representative: Kim Reed Contact the Editors: To contact anyone on the IT Jungle Team Go to our contacts page and send us a message. Sponsored Links Help/Systems: Robot/SCHEDULE Enterprise for UNIX, Linux, Windows & i looksoftware: RPG Open Access Webinar - May 18 at 10am (GMT) & May 19 at 2pm (EDT) Essex Technology Group: May 18-20: IBM POWER7 + COGNOS + VISION, NYC + PA + NJ IT Jungle Store Top Book Picks Easy Steps to Internet Programming for AS/400, iSeries, and S
log in tour help Tour Start 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 developers or posting ads with us Database Administrators Questions Tags Users Badges Unanswered Ask Question _ Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute: Sign up Here's how it works: Anybody can ask a question Anybody can answer The best answers are voted up and rise to the top Avoiding a divide by zero error in a computed column up vote 8 down vote favorite How do you avoid divide by zero error in the below table? CREATE TABLE [dbo].[TblDivision] ( [Numerator] int NOT NULL, [Denominator] int NOT NULL, [Result] AS (Numerator/ Denominator) ) GO Insert into (Numerator, Denominator) TblDivision values (3,0) GO sql-server sql-server-2008-r2 computed-column share|improve this question edited Jan 6 at 12:32 Paul White♦ 29.4k11164263 asked Jan 6 at 12:14 akhil 411 1 If it is invalid for Denominator to be zero you could add a check constraint on that column. That doesn't answer the question though as it only results in a more meaningful error (closer to the source of the problem) rather than avoiding an error completely. –David Spillett Jan 6 at 12:28 Do you intend to use integer division here or not? –Martin Smith Jan 6 at 13:24 Have you considered not allow 0 to be a value in that column? Typically what I've seen is a sproc being used for client applications, rather than plain SQL. –Zymus Jan 7 at 7:11 add a comment| 3 Answers 3 active oldest votes up vote 12 down vote Just add a special case for division by 0: CREATE TABLE [dbo].[TblDivision] ( [Numerator] int NOT NULL, [Denominator] int NOT NULL, [Result] AS case when Denominator=0 then 0 else (Numerator/ Denominator) end ); share|improve this answer answered Jan 6 at 12:19 Phil 20.5k54267 4 Or perhaps return NULL rather than 0 if NULLs are not a problem for code reading that column, as the result of divide-by-zero isn't really 0 unless the numerator is 0. –David Spillett Jan 6 at 12:24 3 Yeah, left as an exercise for the OP, to be honest. Avoid