CODEWITHSUNDEEP
sql-servert-sql
user2387074
user2387074

Reputation: 11

SQL Server : divide by zero error

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!

Upvotes: 1

Views: 391

Answers (3)

canon
canon

Reputation: 41675

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

Upvotes: 3

legohead
legohead

Reputation: 540

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.

Upvotes: 1

Josh Part
Josh Part

Reputation: 2164

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

Upvotes: 0

Related Questions