Divide By Zero Error Encountered Nullif
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 sql divide by zero error encountered nullif of this site About Us Learn more about Stack Overflow the company oracle nullif divide by zero Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges divide by zero error encountered excel Ask Question 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; it only takes a divide by zero error encountered in stored procedure 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 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
Divide By Zero Error Encountered In Crystal Report
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 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
By Chris FehilyJul 25, 2008 Topics: ProductivitySuppose you
Divide By Zero Error Encountered In Sql Server
want to calculate the male–female ratios for various school divide by zero error encountered in sql server 2012 clubs, but you discover that the following query fails and issues a divide-by-zero error when divide by zero error encountered sql server 2008 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 http://stackoverflow.com/questions/19631017/simple-way-to-prevent-a-divide-by-zero-error-in-sql 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 http://www.peachpit.com/blogs/blog.aspx?uk=Avoiding-division-by-zero-with-NULLIF-Five-SQL-Tips-in-Five-Days-Part-5- 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
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 http://www.sql-server-helper.com/error-messages/msg-8134.aspx Messages 11001-11500 Messages 11501-12000 Messages 12001-13000 Messages 13001-13500 Messages 14001-14500 Home>SQL Server Error Messages> Msg 8134 - Divide by zero error encountered SQL Server Error Messages - Msg 8134 - 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. Causes: divide by 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 these divide by zero 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 Server w