This error is caused by performing a division operation wherein the denominator
or the divisor is 0. This error is not encountered when the denominator
or divisor is NULL because this will result to a NULL value.
Check what values you are getting in CAST(b.net_sales_ty AS DECIMAL(19,5)), and CAST(b.net_sales_ly AS DECIMAL(19,5)), may be one of the value is zero, that's why you are getting the error
There are three ways to avoid the "Division by zero encountered" error in your
SELECT statement and these are as follows:
-
CASE statement
-
NULLIF/ISNULL functions
-
SET ARITHABORT OFF and SET ANSI_WARNINGS OFF
Using the CASE statement, your query will look like the following:
SELECT CASE WHEN [Denominator] = 0 THEN 0 ELSE [Numerator] / [Denominator] END AS [Percentage]
FROM [Table1]
If the denominator or divisor is 0, the result becomes 0. Otherwise, the
division operation is performed.
Using the NULLIF and ISNULL functions, your query will look like the following:
SELECT ISNULL([Numerator] / NULLIF([Denominator], 0), 0) AS [Percentage]
FROM [Table1]
What this does is change the denominator into NULL if it is zero. Then in
the division, any number divided by NULL results into NULL. So if the
denominator is 0, then the result of the division will be NULL. Then to
return a value of 0 instead of a NULL value, the ISNULL function is used.
Lastly, using the SET ARITHABORT and SET ANSI_WARNINGS, your query will look
like the following:
SET ARITHABORT OFF
SET ANSI_WARNINGS OFF
SELECT [Numerator] / [Denominator]
With both ARITHABORT and ANSI_WARNINGS set to OFF, SQL Server will return a NULL
value in a calculation involving a divide-by-zero error. To return a 0
value instead of a NULL value, you can put the division operation inside an
ISNULL function:
SET ARITHABORT OFF
SET ANSI_WARNINGS OFF
SELECT ISNULL([Numerator] / [Denominator], 0)