Decimals missing in Count Function

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 2000 Forums

Transact-SQL (2000)

Decimals missing in Count Function

Author

Topic

AndyC
Yak Posting Veteran

53 Posts

Posted - 2004-10-25 : 09:33:40

I am compiling a table of data for a golf competition
Each winning match is worth 1 point, each tied game 0.5 points

It may not be the most elegant method but I can get the number of wins and draws by a player/team as follows

COUNT(CASE WHEN (matches.winner = 'US')THEN 1 END )/4 as Win
COUNT(CASE WHEN (matches.winner = 'Halved')THEN 1 END )/4 as Tie

However when I try to obtain the points total thus
COUNT(CASE WHEN (matches.winner = 'US')THEN 1 END )/4+ COUNT(CASE WHEN (matches.winner = 'Halved')THEN 1 END )/8 as Points

the result gets rounded down. For instance 2 wins and 3 ties results in 3 points rather than 3.5

I have played arounf with CAST and CONVERT but either end up with errors or the same conclusion

Andrew Clark
www.majorleaguecharts.com

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-25 : 09:42:21

try it like this:

cast(COUNT(CASE WHEN (matches.winner = 'US')THEN 1 END ) as decimal(9,4))/4 as Win
cast(COUNT(CASE WHEN (matches.winner = 'Halved')THEN 1 END ) as decimal(9,4))/4 as Tie

and

cast(COUNT(CASE WHEN (matches.winner = 'US')THEN 1 END ) as decimal(9,4))/4
+
cast(COUNT(CASE WHEN (matches.winner = 'Halved')THEN 1 END ) as decimal(9,4))/8
as Points

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources