A quick Solution to Msg 8134, Level 16, State 1, Line 1 Divide by zero error encountered.

How often do you get some bad data and land up with that awesome unexpected 0 creeping into your long running queries.

An example of working out a percentage of a value within a group in sql

 The simplest way of solving this was to use the NullIf  Key. This returns NULL if the value is the the same as the second expression passed to the function. this can be COALESCEd to 1 if found. 

SELECT NULLIF(0,0) will return NULL
SELECT NULLIF(1,0) will return 1.

This is a great way to check if a value is 0. If we get a NULL, then we can now default that number to 1 and avoid the Divide by Zero  error.

select COALESCE(NULLIF(0,0),1) will return 1.

This is a simple and syntactically nice way of updating a query without horrible CASE WHEN statements and WHERE clauses on non indexed values.

More References....

http://msdn.microsoft.com/en-us/library/ms177562.aspx

http://blog.sqlauthority.com/2007/06/22/sql-server-explanation-and-comparison-of-nullif-and-isnull/


Chz

Nice Dnb Break