CODEWITHSUNDEEP
sqlsql-server
Jawwad
Jawwad

Reputation: 13

Division by 0 in SQL Server

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

Answers (2)

amit ramkishor gupta
amit ramkishor gupta

Reputation: 34

You can use case statement like case when divide value =zero then 0 else calculate end

Upvotes: 0

Gordon Linoff
Gordon Linoff

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

Related Questions