Formula issue in SQL when one result has 0 value

Question

I have created a script that does some formulas in the select statement.

SUM(PMCLY.[Net Sale LY]/(PMC.[Net Sales] - PMCLY.[Net Sale LY])) AS 'VAR Vs LY %'

When I run the script I am getting following error :

Divide by zero error encountered.

How can I catch when there is a 0 value and just display 0 instead?

Running the outdated 2005 – ThisIsLegend1016 Dec 19 '18 at 10:20 — ThisIsLegend1016, Dec 19 '18 at 10:20

GMB · Answer 1 · 2018-12-19T10:11:54.770

You want to check if the denumerator of the division is zero, and return NULL instead in this case. This will cause the division to return NULL as well, which is likely what you want.

Without knowing your RDBMS, here is an implementation that relies on a CASEstatement, which is supported in most (if not all) SQL servers.

SUM(PMCLY.[Net Sale LY]/(
    CASE WHEN PMC.[Net Sales] - PMCLY.[Net Sale LY] = 0 
    THEN NULL 
    ELSE PMC.[Net Sales] - PMCLY.[Net Sale LY]
    END
)) AS 'VAR Vs LY %'

If your RDBMS supports NULLIF, then :

SUM(PMCLY.[Net Sale LY]/(
    NULLIF( PMC.[Net Sales] - PMCLY.[Net Sale LY], 0)
)) AS 'VAR Vs LY %'

score 0 · Answer 2 · answered Dec 19 '18 at 10:14

this will work:

SELECT case when mod((5/(decode(0,0,5,1))),1) = 0 then 0 else (5/(decode(0,0,5,1))) end 
FROM dual;
0

SELECT case when mod((5/(decode(2,0,5,2))),1) = 0 then 0 else (5/(decode(2,0,5,2))) 
end 
FROM dual;
 2.5

for your case :

SELECT sum(case when mod((PMCLY.[Net Sale LY]/(decode((PMC.[Net Sales] - 
PMCLY.[Net Sale LY],0,5,(PMC.[Net Sales] - PMCLY.[Net Sale LY]))),1) = 0 
then 0 else (5/(decode((PMC.[Net Sales] - PMCLY.[Net Sale LY],0,5,(PMC.[Net 
Sales] - PMCLY.[Net Sale LY])))) end 
FROM tablename;

SQL_M · Answer 3 · 2018-12-19T10:38:43.500

U can make use of a combination of the NULLIF and ISNULL functions. The NULLIF can be used to prevent the divide by zero errors. The ISNULL can be wrapped on the outside, to set a default value in such cases. In your case, that would be 0.

Something like this:

SELECT ISNULL (10 / NULLIF (0, 0) , 0)  -- result = 0

Your code example would look like this:

ISNULL (SUM (PMCLY.[Net Sale LY] / NULLIF ( (PMC.[Net Sales] - PMCLY.[Net Sale LY]), 0 ) ), 0) AS 'VAR Vs LY %'

Formula issue in SQL when one result has 0 value

3 Answers3