6

I have two columns, both int's, Wins and Losses. I have a calculated column WinPercentage as a decimal(14,3), I want this to be:

WinPercentage = (Wins + Losses) / Wins

What's the syntax for that?

Mahmoud Gamal
  • 78,257
  • 17
  • 139
  • 164
Scott
  • 4,066
  • 10
  • 38
  • 54

1 Answers1

7
CREATE TABLE WinLoss
(
 TeamId int IDENTITY(1,1) NOT NULL,
 Wins int,
 Losses int,
 WinPercentage AS CASE WHEN wins > 0 THEN (Wins + Losses) / Wins ELSE 0 END
)
OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
John Hartsock
  • 85,422
  • 23
  • 131
  • 146
  • If i was just putting this into the column properties toolbox, would I just put in everything after the 'AS' – Scott Nov 04 '10 at 00:55
  • 1
    @Scott .... Yes I believe so.... I have strayed away from using the design tools as I can see more of what is going on when I type it in. But yes I believe you are right. – John Hartsock Nov 04 '10 at 00:57
  • 2
    You want to check the wins value, or risk a division by zero error when selecting records - an insert works fine. – OMG Ponies Nov 04 '10 at 01:05
  • How would I check Wins for 0 and set the column = 0 if this were the case. – Scott Nov 04 '10 at 01:11
  • @Scott look at the edited answer.. OMG Ponies edited the answer. – John Hartsock Nov 04 '10 at 01:12