Division by 0

Posted on 2011-10-20
Last Modified: 2012-05-12

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
Question by:GadFriedman
    LVL 23

    Expert Comment

    by:Rajkumar Gs
    What is the error ?
    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
    LVL 23

    Expert Comment

    by:Rajkumar Gs
    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

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

    Open in new window

    LVL 69

    Expert Comment

    by:É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
    LVL 25

    Expert Comment

    by:Lee Savidge
    You could try this:

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

    Expert Comment

    by:Lee Savidge
    Whoops, I mean this:

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

    Author Closing Comment

    Thank You

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    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…

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now