CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008
Jared
Jared

Reputation: 2133

SQL CASE Statement Not Working Correctly

I have a view in SQL Server 2008 with several columns that are expressions of one column divided by another. I have to account for the divisor being 0, so I use a CASE statement. Here is an example of one:

CASE SUM(dbo.GameStats.BringBacksAttempted) 
  WHEN 0 THEN 
    0 
  ELSE 
    SUM(dbo.GameStats.BringBacks) / SUM(dbo.GameStats.BringBacksAttempted) 
END

Even when SUM(BringBacksAttempted) and SUM(BringBacks) are not 0, the statement is always 0. What am I doing wrong?

Upvotes: 1

Views: 1063

Answers (2)

shahkalpesh
shahkalpesh

Reputation: 33476

What data type is BringBacksAttempted and BringBacks?
If both are int and the result comes to be a fraction, you will only see integer part of it.

e.g. 100 / 250 will return 0.
Whereas, CAST(100 as Decimal) / 250 will return 0.40000

Use a CAST or CONVERT on one of the fields.

Upvotes: 8

OMG Ponies
OMG Ponies

Reputation: 332541

Try:

CASE 
  WHEN SUM(dbo.GameStats.BringBacksAttempted) = 0 THEN 
    0 
  ELSE 
    SUM(dbo.GameStats.BringBacks) / SUM(dbo.GameStats.BringBacksAttempted) 
END

Upvotes: 2

Related Questions