Division By Zero Sql Error
Contents |
By Chris FehilyJul 25, 2008 Topics: ProductivitySuppose you avoid division by zero sql want to calculate the male–female ratios for various school sql server division by zero clubs, but you discover that the following query fails and issues a divide-by-zero error when
Sql Division By Zero Occurred
it tries to calculate ratio for the Lord of the Rings Club, which has no women: SELECT club_id, males, females, males/females AS ratio
Sql Error Divide By Zero Error Encountered
FROM school_clubs; 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: SELECT club_id, males, females, males/NULLIF(females, 0) AS ratio FROM school_clubs; Any number divided by divide by zero sql server 2012 NULL gives NULL, and no error is generated. Be sure to look back over my posts this week for more SQL tips. And for more on SQL, be sure to check out my book SQL: Visual QuickStart Guide, 3rd Edition. Share|Share on twitter Share on facebook Share on google Share on email✉ Other Things You Might LikeData at Work: Best practices for creating effective charts and information graphics in Microsoft Excel By Jorge CamõesBook $35.99Speaker Camp Workshop: Learn by Video By Russ Unger, Samantha StarmerDVD $39.99Windows 8: Visual QuickStart Guide By Barrie SosinskyBook $27.99 View Your Cart Account Sign InSearchSearch View Your Cart 👤 Account Sign In Create AccountTopicsAdobe PhotoshopAppleDesignDigital PhotographyDigital VideoGadgetsProductivity Web Design & Development StoreBookseBooksVideoWeb EditionsAuthorsSafariExploreAbout UsAcademic ResourcesAffiliatesArticlesBlogsNewslettersPressUser GroupsAboutAffiliatesContact UsJobsLegal NoticePrivacy PolicyPressPromotionsSite MapWrite for Us © 2016 Pearson Education, Peachpit. All rights reserved. 1301 Sansome Street, San Francisco, CA 94111
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
Divide By Zero Sql Nullif
Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs oracle division by zero Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just php division by zero like you, helping each other. Join them; 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 http://www.peachpit.com/blogs/blog.aspx?uk=Avoiding-division-by-zero-with-NULLIF-Five-SQL-Tips-in-Five-Days-Part-5- 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 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 http://stackoverflow.com/questions/19631017/simple-way-to-prevent-a-divide-by-zero-error-in-sql 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: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 O
OK, some of the data I am using isnt playing nicely and once in a while I get Divide by zero error encountered. Divide by zero error encountered. Msg 8134, https://ask.sqlservercentral.com/questions/22402/best-way-to-avoid-divide-by-zero.html Level 16, State 1, Line 2 To avoid this I am using a CASE as: DECLARE @Int1 AS INT , @Int2 AS INT SET @Int1 = 6 SET @Int2 = 2 SELECT @Int1 http://blog.sqlauthority.com/2014/12/29/sql-server-puzzle-why-divide-by-zero-error/ / @Int2 SET @Int1 = 6 SET @Int2 = 0 SELECT @Int1 / CASE WHEN @Int2 = 0 THEN 1 ELSE @Int2 END is there a better way of getting the same by zero results but without littering by nice tidy SELECT statements with hundreds of CASE WHEN ... THEN ... ELSE ... END's?? Am I missing some super useful function that does this in one line? [edit]OK, thanks for the answers so far. I dont want to lose rows where it is zero so, I cant accept a NULL in its place or to filter out those division by zero rows with a WHERE @int20 I simply want to replace a 0 value with a 1 value... more ▼ 0 total comments 973 characters / 178 words asked Oct 14, 2010 at 08:54 AM in Default Fatherjack ♦♦ 43.7k ● 79 ● 98 ● 117 edited Oct 14, 2010 at 11:03 AM add new comment (comments are locked) 10|1200 characters needed characters left ▼ Everyone Moderators Original poster and moderators Other... Viewable by all users 4 answers: sort voted first ▼ oldest newest voted first 0 You can use NULLIF if you want to return null when division by zero: SELECT @int1 / NULLIF(@int2,0) -- returns NULL or SELECT @int1 / ISNULL(NULLIF(@int2,0),1) -- returns @int1 The CASE statement is probably much faster then the second option, but you can test the performance more ▼ 11 total comments 288 characters / 46 words answered Oct 14, 2010 at 09:04 AM Håkan Winther 16.5k ● 36 ● 45 ● 57 edited Oct 14, 2010 at 09:07 AM Always though about CASE on this one. Good to know about NULLIF Oct 14, 2010 at 09:07 AM ozamora Håkan Winther Nope, the case statement is n
SERVER - Puzzle - Why Divide by Zero Error December 29, 2014Pinal DaveSQL, SQL Puzzle, SQL Server, SQL Tips and Tricks35 commentsEarlier I asked a puzzle SQL SERVER - Puzzle - Why Decimal is Rounded Up? and it was very well received by all of you. You can read various comments posted in the blog post and they are indeed very enlightening. After posting that blog post, I received quite a lots of emails asking for more puzzle similar to that. Based on your request, here is another puzzle which is very similar to the earlier puzzle but have a very different approach.The question is why following T-SQL gives a famous divide by zero error.SELECT 10000/(17/17/17) AS Result2;When we execute above query it gives following error:Msg 8134, Level 16, State 1, Line 1 Divide by zero error encountered.Here is the screenshot of the error.Hint: Read the comments in this blog post.I will be announcing the winner of this puzzle in my newsletter. There will be a surprise gift of USD 29. Leave your answers in the comment.Reference: Pinal Dave (http://blog.sqlauthority.com) 445 35 comments. Leave new 亂馬客 December 29, 2014 7:06 amInt / int = int 17/17 => 1 1/17 => 0 10000/0 => Divide by zero error encountered. Thanks.Reply Logan December 29, 2014 8:04 amThe denominator resolves to 0 since all numbers are integers and inside of the parenthesis the division is solved from left to right. 17/17 = 1 and then 1/17 = .0588 which is rounded to 0 to maintain the integer data type. Now the numerator is divided by 0 and throws an errorReply Anu December 29, 2014 8:59 amIn this situation SQL will translate 17 as an int so it is rounding the result to 0 and then attempting to divide 10000 by 0. Since division is a distribution and we can't distribute any number against 0. If you would like see SQL perform this math you can write "select 10000/(17.00/17.00/17.00) AS Result2" then we will get result in decimal or float however even this won't be completely accurate because SQL will do some rounding during the calculation.Reply lethalwarriors December 29, 2014 9:33 amHi Pinal, Driving point for the result here is that all the numbers are integer hence the com