SQL CASE Statement Not Working Correctly

Question

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?

Answer 1

Try:

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

Answer 2

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.