Divide by zero error encountered and division

Question

Divide by zero error encountered and division

nymets86
SSC Enthusiast

Points: 191
More actions
April 14, 2020 at 6:27 pm

#3742836

Having trouble with the below statement. Please help if you can. I am not that great with SQL but trying to get better. Thanks in advance. Getting the divide by zero error because of 0s in the numerator.

select CAST(ROUND(COLUMN_A *100.0 / COLUMN_B,2) as decimal(5,2)) as "Percentage" from TABLE

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Answer 1

My expectation is that COLUMN_B has 0 values. So you need to exclude those or do something with them such as setting them to 1. how you handle it depends on your data and what you are trying to do. For example, you could put a where clause on and filter out everything where COLUMN_B is 0 or you could change COLUMN_B to NULL when it is 0, or could use a CASE statement to change it to another value when it is 0.

for suggestion 1:

select CAST(ROUND(COLUMN_A*100/COLUMN_B,2) as decimal(5,2)) as "Percentage" from TABLE WHERE COLUMN_B <> 0

for suggestion 2:

select CAST(ROUND(COLUMN_A*100.0/NULLIF(COLUMN_B,0),2) as decimal(5,2)) as "Percentage" from TABLE

for suggestion 3:

select CAST(ROUNT(COLUMN_A*100.0/(CASE WHEN COLUMN_B = 0 then -1 ELSE COLUMN_B END) as decimal(5,2)) as "Percentage" from TABLE

There may be other solutions too... it really depends on what you want to do when the value is 0.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Answer 2

nymets86

SSC Enthusiast

Points: 191

April 14, 2020 at 7:57 pm

#3742863

thanks so much for the fast reply. I will try all of your solutions then post which one worked for me. much appreciated.

Answer 3

select CAST(ROUND(COLUMN_A*100.0/(CASE WHEN COLUMN_B = 0 then -1 ELSE COLUMN_B END) as decimal(5,2)) as "Percentage" from TABLE

I think this is the one I need but I am getting a syntax error.

Incorrect syntax near the keyword 'AS'.

Do you know what is wrong with this?

I basically want to do the divide calculation and if there is a zero apply a % of 0 to the row.

Answer 4

So, if I understand correctly, if it is going to divide by 0, then you want it to return 0 instead. Then none of the solutions I provided will help actually. In that case, you likely want something like:

SELECT CAST(ROUND(COLUMN_A*100/COLUMN_B,2) as decimal(5,2)) AS "Percentage" 
FROM TABLE 
WHERE COLUMN_B <> 0
UNION ALL
SELECT 0
FROM TABLE
WHERE COLUMN_B = 0

OR

SELECT CASE COLUMN_B 
    WHEN 0 THEN 0
    ELSE CAST(ROUND(COLUMN_A*100/COLUMN_B,2) as decimal(5,2)) 
    END AS "Percentage" 
FROM TABLE

Both should give you the same result, just ordered differently. Different execution plans and thus different performance.

First one does the calculation when the value of COLUMN_B is non-zero and does a UNION to get 0 when it is zero. The second one does that all in a single select...

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Answer 5

Maybe this will give you some ideas:

Declare @testTable Table (column_a int, column_b int);

 Insert Into @testTable (column_a, column_b)
 Values (10,  3)
      , ( 1,  2)
      , (10,  0)
      , ( 1,  5);

 Select *
      , cast(tt.column_a * 100.0 / coalesce(nullif(tt.column_b, 0), tt.column_a) As decimal(5,2))
   From @testTable tt;

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Answer 6

ratbak

Ten Centuries

Points: 1357

April 14, 2020 at 9:21 pm

#3742883

SELECT CAST(ROUND(COLUMN_A*100.0/(CASE WHEN COLUMN_B = 0 THEN -1 ELSE COLUMN_B END),2) as decimal(5,2)) as "Percentage"

You lost the ROUND() function's length parameter in that version

Answer 7

ratbak wrote:

SELECT CAST(ROUND(COLUMN_A*100.0/(CASE WHEN COLUMN_B = 0 THEN -1 ELSE COLUMN_B END),2) as decimal(5,2)) as "Percentage"

You lost the ROUND() function's length parameter in that version

Good catch. That's what I get for posting code without testing it :P. Should have the ",2)" before "as decimal" as you corrected.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Answer 8

nymets86

SSC Enthusiast

Points: 191

April 15, 2020 at 3:56 pm

#3743175

thanks so much to both of you. the last formula worked for me and I appreciate you both taking the time to help me. cheers!