Divide by zero encountered - 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

General SQL Server Forums

New to SQL Server Programming

Divide by zero encountered

Author

Topic

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-06-04 : 06:17:58

I'm getting such error. I want the result as blank once it's divided by zero. Is that possible?

Here's my formula:

SELECT (SUM(CASE WHEN Advoc_InfoCorec = 'No' THEN 1 ELSE 0 END) * 1.0 - SUM(CASE WHEN Info_Ofer = 'Y' THEN 1 ELSE 0 END)
* 1.0 + SUM(CASE WHEN Info_ServPolcy = 'Y' THEN 1 ELSE 0 END) + SUM(CASE WHEN Info_TodAdvan = 'Y' THEN 1 ELSE 0 END)
* 1.0 + SUM(CASE WHEN Info_UseOk = 'Y' THEN 1 ELSE 0 END) * 1.0 + SUM(CASE WHEN Info_MadChang = 'Y' THEN 1 ELSE 0 END) * 1.0)
/ (SUM(CASE WHEN Info_Ofer = 'Y' THEN 1 ELSE 0 END) * 1.0 + SUM(CASE WHEN Info_ServPolcy = 'Y' THEN 1 ELSE 0 END)
* 1.0 + SUM(CASE WHEN Info_TodAdvan = 'Y' THEN 1 ELSE 0 END) * 1.0 + SUM(CASE WHEN Info_UseOk = 'Y' THEN 1 ELSE 0 END)
* 1.0 + SUM(CASE WHEN Info_MadChang = 'Y' THEN 1 ELSE 0 END) * 1.0 + SUM(CASE WHEN Advoc_InfoCorec = 'No' THEN 1 ELSE 0 END)
* 1.0 - SUM(CASE WHEN Info_Ofer = 'Y' THEN 1 ELSE 0 END) * 1.0 + SUM(CASE WHEN Info_ServPolcy = 'Y' THEN 1 ELSE 0 END)
+ SUM(CASE WHEN Info_TodAdvan = 'Y' THEN 1 ELSE 0 END) * 1.0 + SUM(CASE WHEN Info_UseOk = 'Y' THEN 1 ELSE 0 END)
* 1.0 + SUM(CASE WHEN Info_MadChang = 'Y' THEN 1 ELSE 0 END)) * 1.0 AS Info_Other
FROM dbo.TPhones_Oct

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-04 : 06:23:02

quote:
Originally posted by cutiebo2t

I'm getting such error. I want the result as blank once it's divided by zero. Is that possible?

Here's my formula:

SELECT (SUM(CASE WHEN Advoc_InfoCorec = 'No' THEN 1 ELSE 0 END) * 1.0 - SUM(CASE WHEN Info_Ofer = 'Y' THEN 1 ELSE 0 END)
* 1.0 + SUM(CASE WHEN Info_ServPolcy = 'Y' THEN 1 ELSE 0 END) + SUM(CASE WHEN Info_TodAdvan = 'Y' THEN 1 ELSE 0 END)
* 1.0 + SUM(CASE WHEN Info_UseOk = 'Y' THEN 1 ELSE 0 END) * 1.0 + SUM(CASE WHEN Info_MadChang = 'Y' THEN 1 ELSE 0 END) * 1.0)
/ NULLIF((SUM(CASE WHEN Info_Ofer = 'Y' THEN 1 ELSE 0 END),0) * 1.0 + SUM(CASE WHEN Info_ServPolcy = 'Y' THEN 1 ELSE 0 END)
* 1.0 + SUM(CASE WHEN Info_TodAdvan = 'Y' THEN 1 ELSE 0 END) * 1.0 + SUM(CASE WHEN Info_UseOk = 'Y' THEN 1 ELSE 0 END)
* 1.0 + SUM(CASE WHEN Info_MadChang = 'Y' THEN 1 ELSE 0 END) * 1.0 + SUM(CASE WHEN Advoc_InfoCorec = 'No' THEN 1 ELSE 0 END)
* 1.0 - SUM(CASE WHEN Info_Ofer = 'Y' THEN 1 ELSE 0 END) * 1.0 + SUM(CASE WHEN Info_ServPolcy = 'Y' THEN 1 ELSE 0 END)
+ SUM(CASE WHEN Info_TodAdvan = 'Y' THEN 1 ELSE 0 END) * 1.0 + SUM(CASE WHEN Info_UseOk = 'Y' THEN 1 ELSE 0 END)
* 1.0 + SUM(CASE WHEN Info_MadChang = 'Y' THEN 1 ELSE 0 END)) * 1.0 AS Info_Other
FROM dbo.TPhones_Oct

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-06-04 : 06:25:38

It doesn't work

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-04 : 06:36:01

Put NULLIF on ALL sums!

/ NULLIF(SUM(CASE WHEN Info_Ofer = 'Y' THEN 1.0 ELSE 0.0 END + CASE WHEN Info_ServPolcy = 'Y' THEN 1.0 ELSE 0.0 END + CASE WHEN Info_TodAdvan = 'Y' THEN 1.0 ELSE 0.0 END + CASE WHEN Info_UseOk = 'Y' THEN 1.0 ELSE 0.0 END + CASE WHEN Info_MadChang = 'Y' THEN 1.0 ELSE 0.0 END + CASE WHEN Advoc_InfoCorec = 'No' THEN 1.0 ELSE 0.0 END - CASE WHEN Info_Ofer = 'Y' THEN 1.0 ELSE 0.0 END + CASE WHEN Info_ServPolcy = 'Y' THEN 1.0 ELSE 0.0 END + CASE WHEN Info_TodAdvan = 'Y' THEN 1.0 ELSE 0.0 END + CASE WHEN Info_UseOk = 'Y' THEN 1.0 ELSE 0.0 END + CASE WHEN Info_MadChang = 'Y' THEN 1.0 ELSE 0.0 END), 0) AS Info_Other

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-04 : 06:42:04

quote:
Originally posted by cutiebo2t

It doesn't work

why?are you still getting error? can we see the query tried?

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-06-04 : 06:43:13

it works..thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-04 : 06:44:21

[code]SELECT SUM (
CASE WHEN Advoc_InfoCorec = 'No' THEN 1.0 ELSE 0.0 END
- CASE WHEN Info_Ofer = 'Y' THEN 1.0 ELSE 0.0 END
+ CASE WHEN Info_ServPolcy = 'Y' THEN 1.0 ELSE 0.0 END
+ CASE WHEN Info_TodAdvan = 'Y' THEN 1.0 ELSE 0.0 END
+ CASE WHEN Info_UseOk = 'Y' THEN 1.0 ELSE 0.0 END
+ CASE WHEN Info_MadChang = 'Y' THEN 1.0 ELSE 0.0 END
)
/
NULLIF(SUM (
CASE WHEN Info_Ofer = 'Y' THEN 1.0 ELSE 0.0 END -- Used twice
+ CASE WHEN Info_ServPolcy = 'Y' THEN 1.0 ELSE 0.0 END -- Used twice
+ CASE WHEN Info_TodAdvan = 'Y' THEN 1.0 ELSE 0.0 END -- Used twice
+ CASE WHEN Info_UseOk = 'Y' THEN 1.0 ELSE 0.0 END -- Used twice
+ CASE WHEN Info_MadChang = 'Y' THEN 1.0 ELSE 0.0 END -- Used twice
+ CASE WHEN Advoc_InfoCorec = 'No' THEN 1.0 ELSE 0.0 END
- CASE WHEN Info_Ofer = 'Y' THEN 1.0 ELSE 0.0 END -- Zeroes out first occurance
+ CASE WHEN Info_ServPolcy = 'Y' THEN 1.0 ELSE 0.0 END -- Doubles first occurance
+ CASE WHEN Info_TodAdvan = 'Y' THEN 1.0 ELSE 0.0 END -- Doubles first occurance
+ CASE WHEN Info_UseOk = 'Y' THEN 1.0 ELSE 0.0 END -- Doubles first occurance
+ CASE WHEN Info_MadChang = 'Y' THEN 1.0 ELSE 0.0 END -- Doubles first occurance
), 0) AS Info_Other
FROM dbo.TPhones_Oct[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-04 : 06:45:37

E 12°55'05.25"
N 56°04'39.16"

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-06-04 : 06:53:30

I got it. What if I want the results to be blank instead of Zero.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-04 : 07:00:35

quote:
Originally posted by cutiebo2t

I got it. What if I want the results to be blank instead of Zero.

Its better to do this it at your front end. Or if you are so particular you need to CAST the value to varchar before doing it.

BankOfficerHere
Posting Yak Master

124 Posts

Posted - 2008-06-05 : 00:37:05

I still have question though. Some column, I got the results <null>. That shouldn't be, it should be zero. Is that possible?

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-06-05 : 00:38:36

Yes I have the same issue.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-05 : 00:56:43

quote:
Originally posted by BankOfficerHere

I still have question though. Some column, I got the results <null>. That shouldn't be, it should be zero. Is that possible?

is it <null> or NULL? if its NULL use COALESCE(field,0)
and if its <null> which i believe is string value use like this

COALESCE(NULLIF(field,'<null>'),0

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-06-05 : 01:02:04

I got <null>

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-06-05 : 01:03:10

Here's the statment:

(SUM(CASE WHEN Advoc_ResTym = 'No' THEN 1.0 ELSE 0.0 END) - (SUM(CASE WHEN ResTym_DedAir = 'Y' THEN 1.0 ELSE 0.0 END)
+ SUM(CASE WHEN ResTym_Value = 'Y' THEN 1.0 ELSE 0.0 END) + SUM(CASE WHEN ResTym_XcesProb = 'Y' THEN 1.0 ELSE 0.0 END)
+ SUM(CASE WHEN ResTym_Save = 'Y' THEN 1.0 ELSE 0.0 END) + SUM(CASE WHEN ResTym_Rebut = 'Y' THEN 1.0 ELSE 0.0 END)
+ SUM(CASE WHEN ResTym_OverVer = 'Y' THEN 1.0 ELSE 0.0 END) + SUM(CASE WHEN ResTym_TymCons = 'Y' THEN 1.0 ELSE 0.0 END)))
/ NULLIF (SUM(CASE WHEN ResTym_DedAir = 'Y' THEN 1.0 ELSE 0.0 END) + SUM(CASE WHEN ResTym_Value = 'Y' THEN 1.0 ELSE 0.0 END)
+ SUM(CASE WHEN ResTym_XcesProb = 'Y' THEN 1.0 ELSE 0.0 END) + SUM(CASE WHEN ResTym_Save = 'Y' THEN 1.0 ELSE 0.0 END)
+ SUM(CASE WHEN ResTym_Rebut = 'Y' THEN 1.0 ELSE 0.0 END) + SUM(CASE WHEN ResTym_OverVer = 'Y' THEN 1.0 ELSE 0.0 END)
+ SUM(CASE WHEN ResTym_TymCons = 'Y' THEN 1.0 ELSE 0.0 END) + (SUM(CASE WHEN Advoc_ResTym = 'No' THEN 1.0 ELSE 0.0 END)
- (SUM(CASE WHEN ResTym_DedAir = 'Y' THEN 1.0 ELSE 0.0 END) + SUM(CASE WHEN ResTym_Value = 'Y' THEN 1.0 ELSE 0.0 END)
+ SUM(CASE WHEN ResTym_XcesProb = 'Y' THEN 1.0 ELSE 0.0 END) + SUM(CASE WHEN ResTym_Save = 'Y' THEN 1.0 ELSE 0.0 END)
+ SUM(CASE WHEN ResTym_Rebut = 'Y' THEN 1.0 ELSE 0.0 END) + SUM(CASE WHEN ResTym_OverVer = 'Y' THEN 1.0 ELSE 0.0 END)
+ SUM(CASE WHEN ResTym_TymCons = 'Y' THEN 1.0 ELSE 0.0 END))), 0) AS ResTym_Other

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-05 : 02:31:30

This is your statement

SELECT	(
		SUM(CASE WHEN Advoc_ResTym = 'No' THEN 1.0 ELSE 0.0 END)
		- (	SUM(CASE WHEN ResTym_DedAir = 'Y' THEN 1.0 ELSE 0.0 END) 
			+ SUM(CASE WHEN ResTym_Value = 'Y' THEN 1.0 ELSE 0.0 END)
			+ SUM(CASE WHEN ResTym_XcesProb = 'Y' THEN 1.0 ELSE 0.0 END) 
			+ SUM(CASE WHEN ResTym_Save = 'Y' THEN 1.0 ELSE 0.0 END)
			+ SUM(CASE WHEN ResTym_Rebut = 'Y' THEN 1.0 ELSE 0.0 END) 
			+ SUM(CASE WHEN ResTym_OverVer = 'Y' THEN 1.0 ELSE 0.0 END)
			+ SUM(CASE WHEN ResTym_TymCons = 'Y' THEN 1.0 ELSE 0.0 END)
		)
	) 

/ NULLIF (
		SUM(CASE WHEN ResTym_DedAir = 'Y' THEN 1.0 ELSE 0.0 END)
		+ SUM(CASE WHEN ResTym_Value = 'Y' THEN 1.0 ELSE 0.0 END) 
		+ SUM(CASE WHEN ResTym_XcesProb = 'Y' THEN 1.0 ELSE 0.0 END)
		+ SUM(CASE WHEN ResTym_Save = 'Y' THEN 1.0 ELSE 0.0 END) 
		+ SUM(CASE WHEN ResTym_Rebut = 'Y' THEN 1.0 ELSE 0.0 END)
		+ SUM(CASE WHEN ResTym_OverVer = 'Y' THEN 1.0 ELSE 0.0 END) 
		+ SUM(CASE WHEN ResTym_TymCons = 'Y' THEN 1.0 ELSE 0.0 END)
		+ (SUM(CASE WHEN Advoc_ResTym = 'No' THEN 1.0 ELSE 0.0 END) 
		- (	SUM(CASE WHEN ResTym_DedAir = 'Y' THEN 1.0 ELSE 0.0 END)
			+ SUM(CASE WHEN ResTym_Value = 'Y' THEN 1.0 ELSE 0.0 END) 
			+ SUM(CASE WHEN ResTym_XcesProb = 'Y' THEN 1.0 ELSE 0.0 END)
			+ SUM(CASE WHEN ResTym_Save = 'Y' THEN 1.0 ELSE 0.0 END) 
			+ SUM(CASE WHEN ResTym_Rebut = 'Y' THEN 1.0 ELSE 0.0 END)
			+ SUM(CASE WHEN ResTym_OverVer = 'Y' THEN 1.0 ELSE 0.0 END) 
			+ SUM(CASE WHEN ResTym_TymCons = 'Y' THEN 1.0 ELSE 0.0 END)
		)
	), 0) AS ResTym_Other

You do realize that the first part is most certainly negative?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-05 : 02:34:54

Solving all your paranthesises gives this

SELECT	SUM	(
			CASE WHEN Advoc_ResTym = 'No' THEN 1.0 ELSE 0.0 END
			- CASE WHEN ResTym_DedAir = 'Y' THEN 1.0 ELSE 0.0 END
			- CASE WHEN ResTym_Value = 'Y' THEN 1.0 ELSE 0.0 END
			- CASE WHEN ResTym_XcesProb = 'Y' THEN 1.0 ELSE 0.0 END
			- CASE WHEN ResTym_Save = 'Y' THEN 1.0 ELSE 0.0 END
			- CASE WHEN ResTym_Rebut = 'Y' THEN 1.0 ELSE 0.0 END
			- CASE WHEN ResTym_OverVer = 'Y' THEN 1.0 ELSE 0.0 END
			- CASE WHEN ResTym_TymCons = 'Y' THEN 1.0 ELSE 0.0 END
		)
	/NULLIF	(	SUM(CASE WHEN Advoc_ResTym = 'No' THEN 1.0 ELSE 0.0 END)
		), 0) AS ResTym_Other

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources