SQL Server : divide by zero error

Question

I have searched the forums and found several responses on this topic but as I am new to SQL I'm not getting it.

I created a TSQL query that when run returns "Divide by zero error". This is because I am dividing one column by another and there is a zero in one of the records. Ok, I got that part but I can't make heads or tails of the posts explaining how to resolve the issue.

SELECT 
    f.Sales_Rep1 AS 'Sales Rep'
    , SUM(CAST(f.Other1_Revenue + f.Other2_Revenue AS FLOAT)) AS 'MRR'
    , CW.MRR_Goal AS 'MRR Goal'
    , SUM((f.Other1_Revenue + f.Other2_Revenue)/(CW.MRR_Goal)) AS 'Total'
    , SUM(CAST(f.Product_Revenue + f.Service_Revenue AS FLOAT)) AS 'NRR'
    , CW.NRR_Goal AS 'NRR Goal'
    , (cw.MRR_Goal + cw.NRR_Goal)AS 'Total Goal'
FROM 
    dbo.v_rpt_Opportunity AS f 
INNER JOIN
    dbo.v_memberpickerlist AS m ON f.Sales_Rep1 = m.Member_ID 
INNER JOIN
    dbo.CW_SalesGoals AS CW ON CW.Sales_Rep = f.Sales_Rep1
WHERE 
    (f.Expected_Close_Date >= DATEADD(MM, DATEDIFF(MM, 0, GETDATE()), 0)) 
    AND (m.activestatus = 'active') AND (f.Status = 'Won') 
    OR (f.Expected_Close_Date >= DATEADD(MM, DATEDIFF(MM, 0, GETDATE()), 0)) 
    AND (m.activestatus = 'active') 
    AND (f.Status LIKE '%submitted%')
GROUP BY 
    f.Sales_Rep1, CW.MRR_Goal, CW.NRR_Goal, 
    f.Other1_Revenue, f.Other2_Revenue

I know the line that is causing the issue, but I do not know how to resolve it...

SUM((f.Other1_Revenue + f.Other2_Revenue) / (CW.MRR_Goal)) AS 'Total'

What I am trying to do is get the percentage of the goal but I cant get past the division to do that.

Any help would be greatly appreciated. Thank you!

canon · Accepted Answer · 2023-05-18 18:03:57Z

3

You can wrap your divisor with nullif(Divisor, 0) to avoid divide by zero exceptions. When Divisor is 0 we'll use null instead and the entire expression will evaluate to null, our nearest analogue for undefined in SQL.

select
 o.*
,Numerator / nullif(o.Divisor, 0) as Quotient
from (values -- sample values
     (1.0, 0)
    ,(2.0, 1)
    ,(3.0, 2)
) as o(Numerator, Divisor)
order by
 o.Numerator;

Output:

Numerator	Divisor	Quotient
1.0	0	NULL
2.0	1	2.0
3.0	2	1.5

edited May 18, 2023 at 18:03

answered Mar 19, 2015 at 17:59

canon

41.9k10 gold badges77 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Brad Over a year ago

This is definitely the way to go. You don't want to represent div by 0s as 0 because that doesn't make any sense. Null is as close to undefined as it gets.

Josh Part Over a year ago

@Brad if you speak about "things that makes sense", it could be argued that this way doesn't make sense either, as it changes x / 0 to x / NULL so it is considering that 0 = NULL. It is indeed a clearer and shorter way programatically speaking, but is not accurate mathematically speaking. Either way, I agree that NULL is the closest representation of an undefined math value.

Community · Accepted Answer · 2017-05-23 12:06:20Z

1

You could use an IIF:

SUM(IIF(CW.MRR_Goal = 0, 0, (f.Other1_Revenue + f.Other2_Revenue) / (CW.MRR_Goal))

This way if the goal is equal to 0 then it will SUM the 0 rather than trying the division whereas if the goal is not equal to 0 then it will do the division.

More on IIF https://msdn.microsoft.com/en-GB/library/hh213574.aspx

As user3540365 mentioned IIF only applies from SQL Server 2012 as opposed to CASE which applies from SQL Server 2008 onwards see here and is an SQL standard.

edited May 23, 2017 at 12:06

CommunityBot

11 silver badge

answered Mar 19, 2015 at 18:16

legohead

5402 gold badges8 silver badges24 bronze badges

1 Comment

A_Sk Over a year ago

mention SQL Server 2012

Josh Part · Accepted Answer · 2015-03-19 17:59:29Z

0

Use a CASE statement:

SUM(CASE WHEN CW.MRR_Goal <> 0 THEN (f.Other1_Revenue + f.Other2_Revenue)/(CW.MRR_Goal) ELSE 0 END) AS 'Total'

This way, it will perform the division only if the value of CW.MRR_Goal in that record is other than zero; if it's zero, it will return 0

answered Mar 19, 2015 at 17:59

Josh Part

2,16412 silver badges15 bronze badges

1 Comment

benjamin moskovits Over a year ago

IIF was added to SQL Server in 2012 - CASE has been around forever. You may want to explore how to use begin try/begin catch which would handle (unanticipated) problems gracefully.

Collectives™ on Stack Overflow

SQL Server : divide by zero error

3 Answers 3

2 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related