How To Get Around Divide By Zero

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How To Get Around Divide By Zero

Author

Topic

WelshPunk
Yak Posting Veteran

67 Posts

Posted - 2008-05-01 : 11:17:17

I have this sql posted below which sometimes gets a divide by zero error. How can you get around this error please

SELECT
COUNT(DISTINCT dbo.safety_obs_data.form_id) AS TotalObs,
SUM(dbo.safety_obs_data.safe) AS TotalSafe,
SUM(dbo.safety_obs_data.unsafe) AS TotalUnsafe,
CONVERT(decimal(18, 2), (SUM(CASE WHEN (safe) * 100.0) / (SUM(safe) + SUM(unsafe)))AS [% Safe],
SUM(CASE WHEN unobserved = 1 THEN 1 ELSE NULL END) AS NotSeen,
SUM(CASE WHEN made_safe = 1 THEN 1 ELSE NULL END) AS TotalMadeSafe,
SUM(CASE WHEN sap_note = 1 THEN 1 ELSE NULL END) AS TotalSAPNote,
SUM(CASE WHEN honk = 1 THEN 1 ELSE NULL END) AS TotalHonks,
dbo.Employee.emp_user_id
FROM
dbo.safety_obs_data
INNER JOIN dbo.Employee ON dbo.safety_obs_data.create_by_emp_no = dbo.Employee.emp_no
WHERE
(dbo.safety_obs_data.create_dte BETWEEN CONVERT(DATETIME, @start, 102) AND CONVERT(DATETIME, @end, 102))
GROUP BY dbo.Employee.emp_user_id

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-05-01 : 11:24:49

case when b = 0 then **something** else a/b end

Replace **something** with what you want to return if the denominator (b in the above example) is zero.

- Jeff
http://weblogs.sqlteam.com/JeffS

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-05-01 : 11:42:44

alternatives: not better but different :)

select a/nullif(b,0) --this will return NULL instead of devidebyzero error
select isnull(a/nullif(b,0),0) --this will return 0 instead of devidebyzero error

Be One with the Optimizer
TG

Kumar_Anil
Yak Posting Veteran

68 Posts

Posted - 2008-05-01 : 13:26:23

I ran into the same situation before & my humble answer to that was use 'CASE' to evaluate it to '0' or not & then proceed with whatever you are doing...

Kumar_Anil
Yak Posting Veteran

68 Posts

Posted - 2008-05-01 : 13:31:04

This is the actual code snippet that I worked on & this should pretty much stop you from seeing those divide by zero errors...

when a.firstline = 0 then 1
when ea.lev1 = 0 then 1
when a.firstline is null then 1
when ea.lev1 is null then 1
when ea.lev1 > b.firstline then (1-b.firstline/ea.lev1)
when b.firstline > ea.lev1 then (1-ea.lev1/b.firstline)
else 1

WelshPunk
Yak Posting Veteran

67 Posts

Posted - 2008-05-02 : 03:56:12

Sorry but is this code in relationship to the code that I posted or an example of how to resolve the error in a different situation. Sorry for being a newbie! Could someone show me how I could resolve this in my example above please as I find this confusing.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-02 : 04:54:30

quote:
Originally posted by WelshPunk

I have this sql posted below which sometimes gets a divide by zero error. How can you get around this error please

SELECT
COUNT(DISTINCT dbo.safety_obs_data.form_id) AS TotalObs,
SUM(dbo.safety_obs_data.safe) AS TotalSafe,
SUM(dbo.safety_obs_data.unsafe) AS TotalUnsafe,
CONVERT(decimal(18, 2), (SUM(CASE WHEN (safe) * 100.0) / NULLIF((SUM(safe) + SUM(unsafe)),0))AS [% Safe],
SUM(CASE WHEN unobserved = 1 THEN 1 ELSE NULL END) AS NotSeen,
SUM(CASE WHEN made_safe = 1 THEN 1 ELSE NULL END) AS TotalMadeSafe,
SUM(CASE WHEN sap_note = 1 THEN 1 ELSE NULL END) AS TotalSAPNote,
SUM(CASE WHEN honk = 1 THEN 1 ELSE NULL END) AS TotalHonks,
dbo.Employee.emp_user_id
FROM
dbo.safety_obs_data
INNER JOIN dbo.Employee ON dbo.safety_obs_data.create_by_emp_no = dbo.Employee.emp_no
WHERE
(dbo.safety_obs_data.create_dte BETWEEN CONVERT(DATETIME, @start, 102) AND CONVERT(DATETIME, @end, 102))
GROUP BY dbo.Employee.emp_user_id

You can add a NULLIF like this. However i think you have an incomplete CASE construct inside it.

WelshPunk
Yak Posting Veteran

67 Posts

Posted - 2008-05-02 : 05:25:28

Removed the incomplete case and it works great. Thank you once again. This site is so on the case. Heaps of praise

Phill

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-02 : 05:26:56

quote:
Originally posted by WelshPunk

Removed the incomplete case and it works great. Thank you once again. This site is so on the case. Heaps of praise

Phill

You're welcome

. Feel free to post whenever you've a doubt.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources