Divide By Zero - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Divide By Zero

Author

Topic

chipembele
Posting Yak Master

106 Posts

Posted - 2009-05-11 : 03:22:26

Hi
I have a problem with divide by zero errors. I've managed to fix these before by the 'When 0 Then 0 Else' method but this time i'm having some trouble with it.

This is what I'm trying to do

(16to18Retained) + (19plusRetained) + (OtherRetained)/(16to18Starts) + (19plusStarts) + (OtherStarts)

Either side of the equation can end up as a zero. As I say I have fixed these successfully before but this one is a bit more complex as the examples i've seen dont support adding three numbers together on each side then dividing one by the other like above.

Can anyone advise please? Any help appreciated.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-11 : 03:27:32

quote:
Either side of the equation can end up as a zero. As I say I have fixed these successfully before but this one is a bit more complex as the examples i've seen dont support adding three numbers together on each side then dividing one by the other like above.

What do you mean by "don't support adding three numbers together" ?

in your statement, the only division is (OtherRetained) / (16to18Starts)
You only need to check for the value of 16to18Starts = 0

if you want to return 0 when the value of 16to18Starts is 0,


case 
when 16to18Starts <> 0
then (16to18Retained) + (19plusRetained) + (OtherRetained)/(16to18Starts) + (19plusStarts) + (OtherStarts)
else 0
end

KH
[spoiler]Time is always against us[/spoiler]

chipembele
Posting Yak Master

106 Posts

Posted - 2009-05-11 : 03:33:30

Sorry. It should have read

((16to18Retained) + (19plusRetained) + (OtherRetained))/((16to18Starts) + (19plusStarts) + (OtherStarts))

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-11 : 03:35:33

then just change to

case 
when (16to18Starts) + (19plusStarts) + (OtherStarts) <> 0
then ((16to18Retained) + (19plusRetained) + (OtherRetained) / ((16to18Starts) + (19plusStarts) + (OtherStarts))
else 0
end

KH
[spoiler]Time is always against us[/spoiler]

chipembele
Posting Yak Master

106 Posts

Posted - 2009-05-11 : 03:55:26

Thanks. That seems way too easy. I'll have a go.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-05-11 : 09:53:06

Will return null on zero devisor:

((16to18Retained)+(19plusRetained)+(OtherRetained) / NULLIF((((16to18Starts)+(19plusStarts)+(OtherStarts)),0)

Will return zero on zero devisor:

ISNULL(((16to18Retained)+(19plusRetained)+(OtherRetained) / NULLIF((((16to18Starts)+(19plusStarts)+(OtherStarts)),0),0)

CODO ERGO SUM

chipembele
Posting Yak Master

106 Posts

Posted - 2009-05-13 : 04:56:07

Ok, I got this to work without divide by zero errors but now I have another problem. I need to CAST it as a decimal but am struggling.

This is my code that I need to CAST AS DECIMAL(19, 0) or AS DECIMAL(19, 2)

CASE
WHEN [16 to 18 Total Students] + [19+ Total Students After 31st Oct Minus Transfers] + [Under 16 Total Students After 31st Oct Minus Transfers] <> 0
THEN

([16 to 18 Retained Since 1st Nov] + [19+ Retained Since 1st Nov] + [Under 16 Retained Since 1st Nov]) / ([16 to 18 Total Students After 31st Oct Minus Transfers] + [19+ Total Students After 31st Oct Minus Transfers] + [Under 16 Total Students After 31st Oct Minus Transfers])
ELSE 0
END AS Percent

Can anyone assist please?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-13 : 04:58:43

THEN CAST( ... AS DECIMAL(19,2))
ELSE 0.00
END AS Percent

E 12°55'05.63"
N 56°04'39.26"

chipembele
Posting Yak Master

106 Posts

Posted - 2009-05-13 : 05:24:09

I'm now getting an error 'Incorrect syntax near 'CAST', expected 'AS''

This is what I've put down

CASE
WHEN [16 to 18 Total Students] + [19+ Total Students After 31st Oct Minus Transfers] + [Under 16 Total Students After 31st Oct Minus Transfers] <> 0
THEN

CAST(([16 to 18 Retained Since 1st Nov] + [19+ Retained Since 1st Nov] + [Under 16 Retained Since 1st Nov]) / ([16 to 18 Total Students After 31st Oct Minus Transfers] + [19+ Total Students After 31st Oct Minus Transfers] + [Under 16 Total Students After 31st Oct Minus Transfers])) AS DECIMAL(19,2))
ELSE 0
END AS Percent

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-13 : 06:07:05

remove the extra ) before AS DECIMAL

KH
[spoiler]Time is always against us[/spoiler]

chipembele
Posting Yak Master

106 Posts

Posted - 2009-05-13 : 06:28:23

Thanks, but I still get an error.A different one now though. Incorrect syntax near the keyword 'Percent'.

I've tried different combinations of brackets but no luck.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-13 : 06:44:05

Percent is a reserved word.

CASE [16 to 18 Total Students] + [19+ Total Students After 31st Oct Minus Transfers] + [Under 16 Total Students After 31st Oct Minus Transfers]
WHEN 0 THEN 0.00
ELSE
CAST(([16 to 18 Retained Since 1st Nov] + [19+ Retained Since 1st Nov] + [Under 16 Retained Since 1st Nov]) / ([16 to 18 Total Students After 31st Oct Minus Transfers] + [19+ Total Students After 31st Oct Minus Transfers] + [Under 16 Total Students After 31st Oct Minus Transfers]) AS DECIMAL(19,2))
END AS [Percent]

E 12°55'05.63"
N 56°04'39.26"

chipembele
Posting Yak Master

106 Posts

Posted - 2009-05-13 : 07:01:27

Thanks. That completes Ok now. Its unearthed another problem though. 4/5 is coming out as 0.00 when it's clearly 0.8. 6/7 is also coming out at 0.00

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-13 : 07:05:44

integer divide by integer will give u back integer. In you case the numerator and denominator are integer. So the result of the division is also an integer.
4 / 5 will give you 0. The case merely converting 0 to 0.0.

multiply your numerator by 1.0 before dividing
( numerator ) * 1.0 / ( denominator )

KH
[spoiler]Time is always against us[/spoiler]

chipembele
Posting Yak Master

106 Posts

Posted - 2009-05-13 : 07:13:49

Excellent. Thanks khtan. Is there anyway I can have the following. 6/7 to show as 85.71 rather than showing 86.00?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-13 : 08:02:55

Multiply with 100.0 instead of 1.0!

E 12°55'05.63"
N 56°04'39.26"

chipembele
Posting Yak Master

106 Posts

Posted - 2009-05-13 : 08:47:37

quote:
Originally posted by Peso

Multiply with 100.0 instead of 1.0!

E 12°55'05.63"
N 56°04'39.26"

Thanks, but that comes out at 8600.00 rather than 85.71

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-13 : 09:01:41

multiply inside the cast thingy! not outside...

CASE [16 to 18 Total Students] + [19+ Total Students After 31st Oct Minus Transfers] + [Under 16 Total Students After 31st Oct Minus Transfers]
WHEN 0 THEN 0.00
ELSE
CAST( 1.0E * ([16 to 18 Retained Since 1st Nov] + [19+ Retained Since 1st Nov] + [Under 16 Retained Since 1st Nov]) / ([16 to 18 Total Students After 31st Oct Minus Transfers] + [19+ Total Students After 31st Oct Minus Transfers] + [Under 16 Total Students After 31st Oct Minus Transfers]) AS DECIMAL(19,2))
END AS [Percent]

E 12°55'05.63"
N 56°04'39.26"

chipembele
Posting Yak Master

106 Posts

Posted - 2009-05-13 : 09:16:18

THankyou. Sorted Now.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources