Solved

# Division by 0

Posted on 2011-10-20
166 Views

How do I add an if statement to check for sum(n.shippedQtyLy)=0
(sum(n.scanQtyLy)*100)/sum(n.shippedQtyLy) as scanPerQtyLy,

When I try
if sum(n.shippedQtyLy)=0 then 0 else
(sum(n.scanQtyLy)*100)/sum(n.shippedQtyLy) as scanPerQtyLy,
I get a syntax error

select n.category,d.item_desc,n.bill_id,n.sku_no,n.pricegroup,avg(n.retail_pr) as retail_pr,

sum(n.shippedQtyLy) as shippedQtyLy,sum(n.shippedAmoutLy) as shippedAmoutLy,avg(n.shippedItemPriceLy) as shippedItemPriceLy, --shipped last year
sum(n.scanQtyLy) as scanQtyLy,sum(n.scanAmoutLy) as scanAmoutLy,avg(n.scItemPriceLy) as scItemPriceLy,                                 --scan last year
(sum(n.scanQtyLy)*100)/sum(n.shippedQtyLy) as scanPerQtyLy,(sum(n.scanAmoutLy)*100)/sum(n.shippedAmoutLy) as scanPerAmountLy,  --scan % last year
0

LVL 23

Expert Comment

What is the error ?
0

LVL 15

Accepted Solution

You have to use CASE.

CASE WHEN sum(n.shippedQtyLy) = 0 then 0 else
(sum(n.scanQtyLy)*100)/sum(n.shippedQtyLy) END as scanPerQtyLy
0

LVL 23

Expert Comment

If it is 'divide by 0' error, try put some check whereever you are diving

Like - Change this
``````(sum(n.scanQtyLy) * 100) / sum(n.shippedQtyLy)
``````
to
``````CASE sum(n.shippedQtyLy) = 0 THEN 0 ELSE
(sum(n.scanQtyLy) * 100) / sum(n.shippedQtyLy) END
``````
0

LVL 69

Expert Comment

select n.category,d.item_desc,n.bill_id,n.sku_no,n.pricegroup,avg(n.retail_pr) as retail_pr
, sum(n.shippedQtyLy) as shippedQtyLy
,sum(n.shippedAmoutLy) as shippedAmoutLy
,avg(n.shippedItemPriceLy) as shippedItemPriceLy
, sum(n.scanQtyLy) as scanQtyLy
,sum(n.scanAmoutLy) as scanAmoutLy
,avg(n.scItemPriceLy) as scItemPriceLy
, case when isnull(sum(n.shippedQtyLy),0) = 0 then 0 else (sum(n.scanQtyLy)*100)/sum(n.shippedQtyLy) end as scanPerQtyLy
, case when isnull(sum(n.shippedAmoutLy),0) then 0 else (sum(n.scanAmoutLy)*100)/sum(n.shippedAmoutLy) end as scanPerAmountLy
0

LVL 25

Expert Comment

You could try this:

(sum(n.scanQtyLy)*100)/isnull(nullif(sum(sum(n.shippedQtyLy)), 0.0), 1.0)as scanPerQtyLy,
0

LVL 25

Expert Comment

Whoops, I mean this:

(sum(n.scanQtyLy)*100)/isnull(nullif(sum(n.shippedQtyLy), 0.0), 1.0)as scanPerQtyLy
0

Author Closing Comment

Thank You
0

## Featured Post

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
how to add IIS SMTP to handle application/Scanner relays into office 365.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…