Reputation: 13
How to solve divide by zero problem?
I am trying to calculate profit percentage but getting divide by zero error. I am new and tried all most all forum but couldn't find any solution. I'm using SQL Server.
Please help me, where I am making mistake?
SELECT
SO_VInv.AccountNo, SUM(SO_VInv.LineCost) AS "Cost Price",
COUNT(SO_VInv.QuantityThisDel) AS Quantity, COUNT(SO_VInv.InvoiceNo) AS "Number of Invoices",
SUM(SO_VInv.CurrentLineTotal) AS "Sale Price", SUM(SO_VInv.CurrentLineTotal) - SUM(SO_VInv.LineCost) - SUM(SO_VInv.TaxToCharge) AS "Gross Profit",
SUM(SO_VInv.CurrentLineTotal) - SUM(SO_VInv.LineCost) - SUM(SO_VInv.TaxToCharge) / (SUM(SO_VInv.LineCost) - SUM(SO_VInv.TaxToCharge)
FROM
SO_VInv
WHERE
InvDate >= '2019-01-01'
GROUP BY
SO_VInv.AccountNo
Above lines are working fine, but this part is causing an issue
SUM(SO_VInv.CurrentLineTotal) - SUM(SO_VInv.LineCost) - SUM(SO_VInv.TaxToCharge) / (SUM(SO_VInv.LineCost) - SUM(SO_VInv.TaxToCharge)
FROM
SO_VInv
WHERE
InvDate >= '2019-01-01'
GROUP BY
SO_VInv.AccountNo
I tried to solve this problem like this but still getting errors
DECLARE @SalePrice DECIMAL
DECLARE @Tax DECIMAL
DECLARE @CostPrice DECIMAL
DECLARE @Profit DECIMAL
DECLARE @Quantity DECIMAL
DECLARE @Final DECIMAL
SET @SalePrice = (SELECT(SUM(SO_VInv.CurrentLineTotal)) FROM SO_VInv )
SET @Tax = (SELECT(SUM(SO_VInv.TaxToCharge)) FROM SO_VInv )
SET @CostPrice = (SELECT(SUM(SO_VInv.LineCost)) FROM SO_VInv )
SET @Profit = (SELECT(@SalePrice-@Tax) FROM SO_VInv )
SET @Quantity = (SELECT COUNT(SO_VInv.QuantityThisDel)FROM SO_VInv )
SET @Final = (SELECT @SalePrice AS "SalePrice"
FROM SO_VInv
WHERE InvDate >= '2019-01-01'
GROUP BY SO_VInv.AccountNo)
SELECT @Final
Divide by zero error
I tried many possible solution which I found online, including declare and set variable etc. method.
Upvotes: 0
Views: 200
Reputation: 34
You can use case statement like case when divide value =zero then 0 else calculate end
Upvotes: 0
Reputation: 1271231
Use nullif()
:
select x / nullif(y, 0)
This will return null
rather than generate an error.
For your particular statement:
SUM(SO_VInv.CurrentLineTotal) - SUM(SO_VInv.LineCost) - SUM(SO_VInv.TaxToCharge) / NULLIF(SUM(SO_VInv.LineCost) - SUM(SO_VInv.TaxToCharge), 0)
Upvotes: 2