SQL Divide by Zero Error

Question

I have an annoying problem which is stopping me from generating some data; The SQL job has 23 steps in total and fails on the 21st.

-- Step 21 Create the table z1QReportOverview
-- Create z1QReportProjectOverview.sql
-- Project Overview - By Category (Part 4).sql
USE database
SELECT z1QReportProjectOverview1.[ERA Category] AS Category,
z1QReportProjectOverview1.[Total Projects Signed],
z1QReportProjectOverview1.[Total Spend Under Review],
z1QReportProjectOverview1.[Avg. Project Size],
z1QReportProjectOverview2.[Work in Progress],
z1QReportProjectOverview2.[Implemented],
z1QReportProjectOverview2.[No Savings],
z1QReportProjectOverview2.[Lost],
CONVERT(decimal(18,0),[Lost])/CONVERT(decimal(18,0),[Total Projects Signed]) AS [Loss Ratio],
z1QReportProjectOverview2.[Completed],
(
    CONVERT(decimal(18,0),([Completed]+[Implemented]))/
    CONVERT(decimal(18,0),([Completed]+[Implemented]+[Lost]))
) 
AS [Success Ratio],
z1QReportProjectOverview3.[Avg. Spend] AS [Average Spend],
z1QReportProjectOverview3.[Avg. Savings] AS [Average Savings],
z1QReportProjectOverview3.[Avg. Savings %] AS [Average Savings %]
INTO dbo.z1QReportProjectOverview
FROM dbo.z1QReportProjectOverview1
JOIN dbo.z1QReportProjectOverview2
ON (z1QReportProjectOverview1.[ERA Category] = z1QReportProjectOverview2.[ERA    Category])
JOIN dbo.z1QReportProjectOverview3
ON (z1QReportProjectOverview2.[ERA Category] = z1QReportProjectOverview3.[ERA     Category])
ORDER BY Category

I believe I know what is causing the divide by zero error.

The 'Lost' field is made up of three fields and in some cases (very rare) all 3 fields are 0 resulting in a 0 in the 'Lost' field.

I believe this is the main cause of the error but there is a second division there as well, I am pretty rubbish at SQL hence my question:

Where should I put the CASE WHEN clause?

-Most likely written this bit wrong as well :( "CASE When [Lost] = 0 SET [Total Projects Signed] = 0"

Any advice is much appreciated!

You can use a case to yeld ZERO when the divisor is zero or just step out that records at all using a filter to avoid the zero divisor (since division by zero is meaningness it's not rare records steping on it are also meaningness), depends on your requirements — jean, Mar 03 '15 at 14:32
I've tried to type the following code whenever I have typed a "/". I cannot get any macro to do it, so I have to rely on my brain. This is what I try to type: / NULLIF ( divisor, 0). see http://stackoverflow.com/questions/861778/how-to-avoid-the-divide-by-zero-error-in-sql for long explanation — Henrik Staun Poulsen, Aug 31 '16 at 13:52

score 3 · Answer 1 · answered Mar 03 '15 at 11:54

Use NULLIF to handle divide by zero error

........
Isnull(CONVERT(DECIMAL(18, 0), [Lost]) / NULLIF(CONVERT(DECIMAL(18, 0), [Total Projects Signed]), 0), 0) AS [Loss Ratio],
Isnull(CONVERT(DECIMAL(18, 0), ( [Completed] + [Implemented] )) /
                        NULLIF(CONVERT(DECIMAL(18, 0), ( [Completed] + [Implemented] + [Lost] )), 0), 0) AS [Success Ratio], 
........

Mahesh · Accepted Answer · 2015-03-03T13:33:49.820

2

You can use CASE as to check if the dividing value is 0 or not.

CASE WHEN CONVERT(decimal(18,0),[Lost]) <> 0 THEN 
        CONVERT(decimal(18,0),[Lost])/CONVERT(decimal(18,0),[Total Projects Signed]) 
     ELSE 0 END AS [Loss Ratio],
z1QReportProjectOverview2.[Completed],
CASE WHEN CONVERT(decimal(18,0),([Completed]+[Implemented]+[Lost])) <> 0 THEN 
      (CONVERT(decimal(18,0),([Completed]+[Implemented]))/CONVERT(decimal(18,0),([Completed]+[Implemented]+[Lost]))) 
    ELSE 0 END AS [Success Ratio],

edited Mar 03 '15 at 13:33

answered Mar 03 '15 at 11:51

Mahesh

8,694
2
32
53

What happens to the 'z1QReportProjectOverview2.[Completed],' part that is inbetween both statements? :) – Justin Mar 03 '15 at 13:29
You can keep that as it is. I just didn't write it here because it has nothing to do with the case. – Mahesh Mar 03 '15 at 13:33

Tanner · Answer 3 · 2015-03-03T12:01:01.743

2

You can use CASE WHEN like so:

CASE 
     -- check if either value is 0 (which will cause error)
     WHEN CONVERT(decimal(18,0),[Lost]) = 0
       OR CONVERT(decimal(18,0),[Total Projects Signed]) = 0
     -- if so, return 0
     THEN 0
     -- otherwise perform calculation
     ELSE CONVERT(decimal(18,0),[Lost])/CONVERT(decimal(18,0),[Total Projects Signed])
END AS [Loss Ratio]

edited Mar 03 '15 at 12:01

answered Mar 03 '15 at 11:53

Tanner

22,205
9
65
83

1

Thank you for the fast feedback. Commenting the code helped me a lot to understand how you've coded it. After the END AS[Loss Ratio] there is z1QReportProjectOverview2.[Completed], ( #Code ) AS [Success Ratio], Will this part still run after the END AS part? – Justin Mar 03 '15 at 12:45
@Justin no problem. This was an example of adding checks to one of the columns in your query. You can use it on the other field too if required by substituting the fields you want to check into the `WHEN` part and only doing the `ELSE` part if the values are suitable. – Tanner Mar 03 '15 at 14:01

score 0 · Answer 4 · answered Mar 03 '15 at 14:21

@Tanner:

So laying out the code like this should work just fine right?

CONVERT(decimal(18,0),[Lost])/CONVERT(decimal(18,0),[Total Projects Signed]) AS [Loss Ratio],
CASE 
 -- check if either value is 0 (which will cause error)
 WHEN CONVERT(decimal(18,0),[Lost]) = 0
   OR CONVERT(decimal(18,0),[Total Projects Signed]) = 0
 -- if so, return 0
 THEN 0
 -- otherwise perform calculation
ELSE CONVERT(decimal(18,0),[Lost])/CONVERT(decimal(18,0),[Total Projects Signed])
END AS [Loss Ratio]
z1QReportProjectOverview2.[Completed],
(
    CONVERT(decimal(18,0),([Completed]+[Implemented]))/
    CONVERT(decimal(18,0),([Completed]+[Implemented]+[Lost]))
) 
AS [Success Ratio],

I believe I should just take out the top line. :) – Justin Mar 03 '15 at 14:22 — Justin, Mar 03 '15 at 14:22

SQL Divide by Zero Error

4 Answers4