Formula issue in SQL when one result has 0 value [duplicate]

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 %'

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;

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 %'