iif calculation help

[orangeblue]

ok i have to create a db that calculates the averages of batemsn in cricket

so the forumla is Avg=totalruns / innings
the problem is that inning can be 0 in which case
Avg=totalruns

so i am confused how i goabout doing this.
so this is the basic if statement
if innings>0 then
Avg=totalruns / innings
else
Avg=totalruns
end if

please can anyone help
Hesh

 

[lespaul]

SELECT Innings, totalRuns, iif(innings>0, TotalRuns/Innings, TotalRuns) As BattingAverage FROM tableName

Leslie

In times of universal deceit, telling the truth will be a revolutionary act. - George Orwell

 

[earthandfire]

IIf(Innings = 0, TotalRuns, TotalRuns/Innings)

 

[orangeblue]

Wicked thanks guys

 

[PHV]

Another way:
TotalRuns/IIf(Innings=0,1,Innings)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ProgramError]

PHV has a cunning way of thinking compaired to the rest of us... something which I admire.


Program Error
Programmers do it one finger at a time!

 

[earthandfire]

PHV, the only argument I would have with your solution is that the division would be carried out regardless of the number of innings, which isn't necessary if innnings = 0.

 

[Golom]

earthandfire

True ... but the solution

IIf(Innings = 0, TotalRuns, TotalRuns/Innings)

does the division anyway because both options are evaluated in an IFF expression even though only one of them is used. Fortunately, using an IFF in SQL where division by zero occurs doesn't do any harm because SQL ignores the "Division by Zero" error unless that option is selected.

 

[earthandfire]

Golom, yes. I'd forgotton that unlike IF, IIf evaluates both the true and false regardless of the outcome. Well spotted and sorry PHV, you solution is more efficient.