Problem with coalesce - 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

Problem with coalesce

Author

Topic

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-06-10 : 02:42:22

I'm still getting error messsage that state "encountered dividing by zero:

Is there a problem with my statement?

SELECT COALESCE (SUM(CASE WHEN ResDrec_MbrQ = 'Y' THEN 1.0 ELSE 0.0 END) * 1.0 / (SUM(CASE WHEN ResDrec_MbrQ = 'Y' THEN 1.0 ELSE 0.0 END)
+ SUM(CASE WHEN ResDrec_PnaltyBox = 'Y' THEN 1.0 ELSE 0.0 END) + SUM(CASE WHEN ResDrec_UnesryTrans = 'Y' THEN 1.0 ELSE 0.0 END)
+ SUM(CASE WHEN ResDrec_MbrAvoid = 'Y' THEN 1.0 ELSE 0.0 END) + SUM(CASE WHEN ResDrec_RefTrans = 'Y' THEN 1.0 ELSE 0.0 END)
+ (SUM(CASE WHEN Advoc_ResDrec = 'No' THEN 1.0 ELSE 0.0 END) - (SUM(CASE WHEN ResDrec_MbrQ = 'Y' THEN 1.0 ELSE 0.0 END)
+ SUM(CASE WHEN ResDrec_PnaltyBox = 'Y' THEN 1.0 ELSE 0.0 END) + SUM(CASE WHEN ResDrec_UnesryTrans = 'Y' THEN 1.0 ELSE 0.0 END)
+ SUM(CASE WHEN ResDrec_MbrAvoid = 'Y' THEN 1.0 ELSE 0.0 END) + SUM(CASE WHEN ResDrec_RefTrans = 'Y' THEN 1.0 ELSE 0.0 END)))), 0)
AS ResDrec_MbrQ
FROM limiaca.TPhones_Oct

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-10 : 02:58:14

Again? Haven't you learned anything from previous posts?

SELECT	SUM(	CASE WHEN ResDrec_MbrQ = 'Y' THEN 1.0 ELSE 0.0 END	)
	/ NULLIF(
	SUM(	+ CASE WHEN ResDrec_PnaltyBox = 'Y' THEN 2.0 ELSE 0.0 END
		+ CASE WHEN ResDrec_UnesryTrans = 'Y' THEN 2.0 ELSE 0.0 END
		+ CASE WHEN ResDrec_MbrAvoid = 'Y' THEN 2.0 ELSE 0.0 END
		+ CASE WHEN ResDrec_RefTrans = 'Y' THEN 2.0 ELSE 0.0 END
		+ CASE WHEN Advoc_ResDrec = 'No' THEN 1.0 ELSE 0.0 END
	), 0) AS ResDrec_MbrQ
FROM	limiaca.TPhones_Oct

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