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

General SQL Server Forums

New to SQL Server Programming

divide by zero

Author

Topic

artbishop
Starting Member

18 Posts

Posted - 2008-06-05 : 01:58:57

SELECT CASE WHEN (SUM(CASE WHEN Rspec_Vulgar = 'Y' THEN 1 ELSE 0 END) + SUM(CASE WHEN Rspec_Vulgar = 'N' THEN 1 ELSE 0 END))
= 0 THEN '0' ELSE (SUM(CASE WHEN Rspec_Vulgar = 'Y' THEN 1 ELSE 0 END) * 1.0) / (SUM(CASE WHEN Rspec_Vulgar = 'Y' THEN 1 ELSE 0 END)
+ SUM(CASE WHEN Rspec_Rude = 'Y' THEN 1 ELSE 0 END)) END AS Rspec_Vulgar
FROM dbo.TekliveQuery

---
Note:
How can I avoid the the error message if the dividend and the divisor are both 0?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-05 : 02:02:19

[code]SELECT CASE WHEN (SUM(CASE WHEN Rspec_Vulgar = 'Y' THEN 1 ELSE 0 END) + ISNULL(SUM(CASE WHEN Rspec_Vulgar = 'N' THEN 1 ELSE 0 END))
= 0 THEN '0' ELSE (SUM(CASE WHEN Rspec_Vulgar = 'Y' THEN 1 ELSE 0 END) * 1.0) / NULLIF((SUM(CASE WHEN Rspec_Vulgar = 'Y' THEN 1 ELSE 0 END)
+ SUM(CASE WHEN Rspec_Rude = 'Y' THEN 1 ELSE 0 END)),0) END,0) AS Rspec_Vulgar
FROM dbo.TekliveQuery[/code]

artbishop
Starting Member

18 Posts

Posted - 2008-06-05 : 02:08:29

quote:

Originally posted by visakh16

SELECT CASE WHEN (SUM(CASE WHEN Rspec_Vulgar = 'Y' THEN 1 ELSE 0 END) + ISNULL(SUM(CASE WHEN Rspec_Vulgar = 'N' THEN 1 ELSE 0 END)) 
= 0 THEN '0' ELSE (SUM(CASE WHEN Rspec_Vulgar = 'Y' THEN 1 ELSE 0 END) * 1.0) / NULLIF((SUM(CASE WHEN Rspec_Vulgar = 'Y' THEN 1 ELSE 0 END) 
+ SUM(CASE WHEN Rspec_Rude = 'Y' THEN 1 ELSE 0 END)),0) END,0) AS Rspec_Vulgar
FROM dbo.TekliveQuery

--

I am getting the error message "[Microsoft][ODBC SQL Server Driver][SQL Server] The isnull function requires 2 arguments."

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-05 : 02:20:10

[code]SELECT CASE
WHEN (SUM(CASE WHEN Rspec_Vulgar = 'Y' THEN 1 ELSE 0 END) + SUM(CASE WHEN Rspec_Vulgar = 'N' THEN 1 ELSE 0 END))
= 0
THEN '0'
ELSE ISNULL((SUM(CASE WHEN Rspec_Vulgar = 'Y' THEN 1 ELSE 0 END) * 1.0) / NULLIF((SUM(CASE WHEN Rspec_Vulgar = 'Y' THEN 1 ELSE 0 END)
+ SUM(CASE WHEN Rspec_Rude = 'Y' THEN 1 ELSE 0 END)),0),0)
END AS Rspec_Vulgar
FROM dbo.TekliveQuery[/code]

artbishop
Starting Member

18 Posts

Posted - 2008-06-05 : 02:24:02

cool! it's now working...thanks much! =)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-05 : 02:25:27

[code]SELECT SUM(
CASE WHEN Rspec_Vulgar IN ('Y', 'N') THEN 1.0 ELSE 0.0 END)
)
/ NULLIF(
SUM(
CASE WHEN 'Y' IN (Rspec_Vulgar, Rspec_Rude) THEN 1.0 ELSE 0.0 END
), 0) END AS Rspec_Vulgar
FROM dbo.TekliveQuery[/code]

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