Divide By Zero Error - 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 Error

Author

Topic

brmcdani441
Starting Member

7 Posts

Posted - 2011-10-19 : 16:14:02

How can I avoid the divide by zero error in this code?

Thanks in advance!

SELECT
SUM(MurderMonthCount) AS MonthlyMurderSum,
SUM(MurderMonthCleared) AS MonthlyMurderClearedSum,
SUM(MurderMonthCleared)/SUM(MurderMonthCount) * 100 AS Test1
FROM #tmp1

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-10-19 : 16:37:00

One way is to use the NULLIF function:

SELECT
SUM(MurderMonthCount) AS MonthlyMurderSum,
SUM(MurderMonthCleared) AS MonthlyMurderClearedSum,
SUM(MurderMonthCleared)/ NULLIF(SUM(MurderMonthCount), 0) * 100 AS Test1
FROM #tmp1

you can also add a COALESCE if you want the result to be 0 and not NULL.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources