Link to home
Start Free TrialLog in
Avatar of GadFriedman
GadFriedman

asked on

Division by 0




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
Avatar of Rajkumar Gs
Rajkumar Gs
Flag of India image

What is the error ?
ASKER CERTIFIED SOLUTION
Avatar of tim_cs
tim_cs
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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) 

Open in new window

to
CASE sum(n.shippedQtyLy) = 0 THEN 0 ELSE
(sum(n.scanQtyLy) * 100) / sum(n.shippedQtyLy) END

Open in new window

Avatar of Éric Moreau
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
You could try this:

(sum(n.scanQtyLy)*100)/isnull(nullif(sum(sum(n.shippedQtyLy)), 0.0), 1.0)as scanPerQtyLy,
Whoops, I mean this:

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

ASKER

Thank You