Divide by Zero

Question

Post reply

Divide by Zero

nfpacct

Right there with Babe

Points: 718
More actions
May 8, 2011 at 11:48 pm

#235841

I am getting a divide by zero error on the following statement that I have. I tried adding the isnull syntax to the statement but was not able to resolve the error. I know that a couple of rows where the sum(curamount) is zero.
"cast(curamount/sum(curamount) over (partition by employeeid,docdate) as decimal (10,4)) as Total"
I would appreciate any input to get over this error.
Thanks

Viewing 12 posts - 1 through 11 (of 11 total)

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

ModestyBlaise84 SSCommitted Points: 1931 More actions · Answer 1

Hi

The run time error you are getting is mathematical issue.

Any number different to 0 or infinite, when divided by 0 is infinite.

As it is often said (although not 100% correct): "Division by 0 is not allowed"

The answer to your question depends on what you want to return in case when sum(curamount) is 0?

You might want to skip those records altogether or you need to show 0? Or something else?

Could you please let us know what is needed in the resultset?

Also, would be probably good idea to post the whole query, not just one line.

Thanks.

C.K.Shaiju SSCarpal Tunnel Points: 4228 More actions · Answer 2

Can you replace those Nulls and Zeros to 1 before running the query?

Or you can use a CASE WHEN to replace it in the query, but using CASE WHEN degrades the performance.

_____________________________________________
One ounce of practice is more important than tonnes of dreams

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

"cast(curamount/ISNULL(NULLIF(sum(curamount), 0), 1) over (partition by employeeid,docdate) as decimal (10,4)) as Total"

nfpacct Right there with Babe Points: 718 More actions · Answer 4

Thank you for your response. I would like to show a zero when the divide by zero error is encountered.

I know I can use the ISNull and ifnull but I am struggling as to where to place it in the syntax.

Thanks

ModestyBlaise84 SSCommitted Points: 1931 More actions · Answer 5

It seems to me that Ninja’s_RGR’us solution will not work.

Tried to create something similar, but it reports the error:

“Msg 4113, Level 15, State 1, Line 1

"ISNULL" is not a valid windowing function, and cannot be used with the OVER clause.”

I believe that this cannot be handled with NULLIF and ISNULL because if curamount values are both positive and negative, it is still possible to get sum=0 (over partition) and separate curamount values different to 0.

For example there would be case

-2/1

2/1

over same partition, while actually, it should be

0-2/(2+(-2))

02/(2+(-2))

My suggestion would be:

Select case when SubTotal =0 then 0

else CAST(curamount/SubTotal as decimal(10,4))

end as Total from

(

select curamount,SUM(curamount) over (partition by employeeid,docdate) as SubTotal from TAbleName

) as X

Hope that somoene will come up with something better, this is really interesting problem (to handle it in one line of code – couldn’t see the way how to do it)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

An easy solution could be to do it in 2 steps (easier on the eyes too).

Get all the sums > derived table

ROW_NUMBER () OVER(col1 / case when col2 <> then col2 else 1 end) >> derived table again

CASE WHEN col2 = 2 then 0 else Calculated_col3 end

If that doesn't work I'll do a fully tested solution tommorrow. I'm sure the case idea works in the over(). I had never tried isnull(nullif)) in there b4. Sorry for the bad solution.

ModestyBlaise84 SSCommitted Points: 1931 More actions · Answer 7

Seems that I managed to find a way to use CASE with OVER:

select Case SUM(curamount over (partition by employeeid,docdate)

when 0 then 0

else curamount/SUM(curamount) over(partition by employeeid,docdate)

end

from TableName

Thanks Ninja, your answer gave me the idea how to do it.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

Awesome, thanks for posting the final answer. Will surely help someone someday.

JPHK SSC Enthusiast Points: 130 More actions · Answer 9

I'm the someone it surely helped today. Thanks! I had the concept right with the CASE but couldn't figure out exactly where it went with the OVER part.

~ J

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 10

T-SQL incorrectly converts blanks to zero when they are cast as numerics. ARRRRGH! :hehe:

select cast('' as numeric) some_column -- blank

some_column

---------------------------------------

Msg 8114, Level 16, State 5, Line 1

Error converting data type varchar to numeric.

select cast(' ' as numeric) some_column -- space

some_column

---------------------------------------

Msg 8114, Level 16, State 5, Line 1

Error converting data type varchar to numeric.

select cast('0' as numeric) some_column -- number

some_column

---------------------------------------

0

clubbavich Default port Points: 1449 More actions · Answer 11

Would this not work?

CAST(ISNULL(curamount/NULLIF(SUM(curamount),0),0)

OVER (PARTITION BY employeeid,docdate) AS decimal(10,4)) AS Total

If SUM(curamount) is 0, return NULL, else return SUM(curamount). X / NULL is NULL (Same with +,-,*). If Result is NULL, return 0, else return Result.