Divide By Zero Error Sql
Contents |
By Chris FehilyJul 25, 2008 Topics: ProductivitySuppose you divide by zero error sql server want to calculate the male–female ratios for various school sql divide by zero error encountered clubs, but you discover that the following query fails and issues a divide-by-zero error when sql divide by zero error nullif 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
How To Fix Divide By Zero Error In Sql Server
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 sql nullif function 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 Messages 11001-11500 Messages 11501-12000 Messages 12001-13000 Messages 13001-13500 Messages 14001-14500 sql iszero Home>SQL Server Error Messages> Msg 8134 - Divide by zero error encountered SQL Server
Tsql Divide By Zero Error Encountered
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
Msg 8134, Level 16, State 1, Line 1 Divide by zero error encountered. Causes: This error is caused by performing a division operation wherein the denominator or the divisor is 0. This error is not http://www.peachpit.com/blogs/blog.aspx?uk=Avoiding-division-by-zero-with-NULLIF-Five-SQL-Tips-in-Five-Days-Part-5- 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 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 http://www.sql-server-helper.com/error-messages/msg-8134.aspx 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 will return a NULL value in a calculation involving a divide-by-zero error. To return a 0 value instead of a NULL value, you can put the division operation inside an ISNULL function: SET ARITHABORT OFF SET ANSI_WARNINGS OFF SELECT ISNULL([Numerator] / [Denominator], 0) Related Articles : Frequently Asked Questions - SQL Server Error Messages Frequently Asked Questions - INSERT Statement Frequently Asked Questions - SELECT Statement
To Prevent Divide-By-Zero Errors In SQL By Ben Nadel on October 3, 2007 Tags: SQL Boyan Kostadinov just sent me a cool https://www.bennadel.com/blog/984-using-nullif-to-prevent-divide-by-zero-errors-in-sql.htm link to an article that is the final part in a four part series that discusses the SQL NULL value. I haven't read the first three parts yet, but there http://dba.stackexchange.com/questions/125363/avoiding-a-divide-by-zero-error-in-a-computed-column is a really cool tip in the fourth part on using NULLIF() to prevent divide-by-zero errors in a SQL call.The idea here is that, as with any other form of divide by math that I know of, you cannot divide by zero in a SQL call. Therefore, running this code:
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. Avoiding the error has been answered :) –Phil Jan 6 at 12:25 add a comment| up vote 10 down vote Similar to @Phil's solution: CREATE TABLE dbo.TblDivision ( Numerator int NOT NULL , Denominator int NOT NULL , Result AS Numerator / nullif(Denominator,0) ); If Denominator is 0 it i