Divide by zero error unique zeros [duplicate]

Question

I have the following table:

Item         Ordqty Total_Costprice TotalSaleprice  onhand  Markup
ENG-MAN-0102                3852    203.34           2494     73.992
SPG-P-018                   2716   1232.80      473.2232
A8                          8.62    9.335                    0.71
A136                        1621    148.35           518    0.3777
LA                  1228    7.68    14.897                   7.217
ENG-DOR             1039    34.94   50.8166                15.8766
A13-00-S               968  153.64    107                   0.9997

Code is

SELECT
    total_costprice, 
    markup,
    CASE WHEN markup=0 THEN 0 ELSE 100*(markup)/costprice AS pctmarkup`

This gives a divide by zero error. I need to show the percentage markup for the markup values.

You would get a syntax error with that code before you got a divide by 0 error — dfundako
– dfundako, Commented Jan 17, 2018 at 15:07
You want to check if your denominator is a 0 before dividing, not your numerator. CASE WHEN costprice = 0 THEN 0 ELSE... — JNevill
– JNevill, Commented Jan 17, 2018 at 15:15
Regarding your syntax error, you need an END to conclude your CASE statement. I would also change your percentage formula to put your division inside of parentheses, not just your numerator. 100 * (markup/costprice) — JNevill
– JNevill, Commented Jan 17, 2018 at 15:17

Mazhar · Accepted Answer · 2018-01-17 15:18:32Z

1

You need to use NULLIF function

select
   total_costprice
   ,markup
   ,case when markup=0 then 0 else 100*(markup/NULLIF(costprice,0)) END as pctmarkup

answered Jan 17, 2018 at 15:18

Mazhar

3,8431 gold badge14 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Spinx Over a year ago

thanks @Cool_Br33ze works fine

SqlKindaGuy · Accepted Answer · 2018-01-17 15:23:06Z

Based on your values this will work. I inserted 0 where you dont have any data - I dont know if that is true.

declare @myt table (item nvarchar(50),ordqty int, total_costprice numeric(18,4),totalsalesprice numeric(18,4),onhand numeric(18,4),markup numeric(18,4)
)

insert into @myt
values 


('ENG-MAN-0102',      0    ,   3852   ,  203.34   ,        2494     , 73.992    ),
('SPG-P-018'   ,      0    ,   2716   ,  1232.80  ,        473.2232 , 0         ),
('A8'          ,      0    ,   8.62   ,  9.335    ,        0        , 0.71      ),
('A136'        ,      0    ,   1621   ,  148.35   ,        518      , 0.3777    ),
('LA'          ,      1228 ,   7.68   ,  14.897   ,        0        , 7.217     ),
('ENG-DOR'     ,      1039 ,   34.94  ,  50.8166  ,        0        , 15.8766   ),
('A13-00-S'    ,      968  ,   153.64 ,  107      ,        0        , 0.9997   )

select * ,CASE WHEN markup=0 THEN 0 ELSE 100*(markup)/total_costprice end as Pct from @myt

Result

Collectives™ on Stack Overflow

Divide by zero error unique zeros [duplicate]

2 Answers 2

1 Comment

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Linked

Related