Divide by zero error unique zeros

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, Jan 17 '18 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, Jan 17 '18 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, Jan 17 '18 at 15:17
@Spinx I guess I'll have to take your word for it since you didn't include any sample data for your `costprice` column. — JNevill, Jan 17 '18 at 15:20

score 1 · Answer 1 · answered Jan 17 '18 at 15:18

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 '18 at 15:18

Mazhar

3,797
1
12
29

thanks @Cool_Br33ze works fine – Spinx Jan 17 '18 at 15:34

score 0 · Answer 2 · answered Jan 17 '18 at 15:23

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

Divide by zero error unique zeros

2 Answers2