Help with division in SQL - 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 2008 Forums

Transact-SQL (2008)

Help with division in SQL

Author

Topic

i11usive
Starting Member

4 Posts

Posted - 2011-11-23 : 04:38:57

I'm trying to get this percentage function to work, but both calculated columns may contain zeros. How can I avoid the "divide by zero" error in SQL?

(SELECT COUNT(*) FROM tResults WHERE HomePlayer = @PlayerId) + (SELECT COUNT(*) FROM tResults WHERE AwayPlayer = @PlayerId) / (SELECT COUNT(*) FROM tResults WHERE Winner = @PlayerId) AS PercWins

Any help would be greatly appreciated.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-11-23 : 04:46:25

use NULLIF ( <denominator value> , 0)

It will return NULL if the denominator is 0

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

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-23 : 04:48:40

use a case statement to exclude zeros. Also might want to multiply by 1.0 to avoid rounding
with cte as
(
select
HomePlayers = (SELECT COUNT(*) FROM tResults WHERE HomePlayer = @PlayerId) ,
AwayPlayers = (SELECT COUNT(*) FROM tResults WHERE AwayPlayer = @PlayerId) ,
Winners = (SELECT COUNT(*) FROM tResults WHERE Winner = @PlayerId) AS PercWins
)
select PercWins = 1.0 * HomePlayers + case when Winners = 0 then 0 else 1.0 * AwayPlayers / Winners) end

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2011-11-23 : 04:52:06

Something like this maybe:


SELECT ISNULL(NULLIF((SELECT COUNT(*) FROM sys.columns WHERE name = 'john'),0) + NULLIF((SELECT COUNT(*) FROM sys.columns WHERE name = 'john1'),0),-1) / 
(SELECT ISNULL(NULLIF((SELECT COUNT(*) FROM sys.columns WHERE name = 'bob'),0),1)) AS PercWins

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-11-23 : 04:58:35


select	count(case when @PlayerId in (HomePlayer, AwayPlayer) then 1 end) * 1.0
	/ count(case when @PlayerId in (Winner) then 1 end) AS PercWins
from	tResults
where	@PlayerId in (HomePlayer, AwayPlayer, Winner)

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

i11usive
Starting Member

4 Posts

Posted - 2011-11-23 : 05:16:20

Thanks guys - I'll try your suggestions and revert.

i11usive
Starting Member

4 Posts

Posted - 2011-11-23 : 06:23:38

Thanks guys .. managed to get it working ...

One more thing, how can I add a row number?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-11-23 : 07:24:59