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
When I try
if sum(n.shippedQtyLy)=0 then 0 else
(sum(n.scanQtyLy)*100)/sum
I get a syntax error
select n.category,d.item_desc,n.b
sum(n.shippedQtyLy) as shippedQtyLy,sum(n.shipped
sum(n.scanQtyLy) as scanQtyLy,sum(n.scanAmoutL
(sum(n.scanQtyLy)*100)/sum
What is the error ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If it is 'divide by 0' error, try put some check whereever you are diving
Like - Change this
Like - Change this
(sum(n.scanQtyLy) * 100) / sum(n.shippedQtyLy)
toCASE sum(n.shippedQtyLy) = 0 THEN 0 ELSE
(sum(n.scanQtyLy) * 100) / sum(n.shippedQtyLy) END
select n.category,d.item_desc,n.b ill_id,n.s ku_no,n.pr icegroup,a vg(n.retai l_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.shipped QtyLy) end as scanPerQtyLy
, case when isnull(sum(n.shippedAmoutL y),0) then 0 else (sum(n.scanAmoutLy)*100)/s um(n.shipp edAmoutLy) end as scanPerAmountLy
, sum(n.shippedQtyLy) as shippedQtyLy
,sum(n.shippedAmoutLy) as shippedAmoutLy
,avg(n.shippedItemPriceLy)
, sum(n.scanQtyLy) as scanQtyLy
,sum(n.scanAmoutLy) as scanAmoutLy
,avg(n.scItemPriceLy) as scItemPriceLy
, case when isnull(sum(n.shippedQtyLy)
, case when isnull(sum(n.shippedAmoutL
You could try this:
(sum(n.scanQtyLy)*100)/isn ull(nullif (sum(sum(n .shippedQt yLy)), 0.0), 1.0)as scanPerQtyLy,
(sum(n.scanQtyLy)*100)/isn
Whoops, I mean this:
(sum(n.scanQtyLy)*100)/isn ull(nullif (sum(n.shi ppedQtyLy) , 0.0), 1.0)as scanPerQtyLy
(sum(n.scanQtyLy)*100)/isn
ASKER
Thank You