Query

  • Thread starter Thread starter Paid The Umpire
  • Start date Start date
P

Paid The Umpire

Quick question, I've got the below SQl statement which works well,
except I want the "CountOfOuts" to count all the values in the table
except those which have the value "NO" in it.

Is there a simple way?



SELECT DISTINCTROW MatchDetails.PlayerName,
Count(MatchDetails.Position) AS Innings, Count(MatchDetails.HowOut) AS
CountOfOuts, Sum(MatchDetails.Runs) AS Runs, [Runs]/Count([Position])
AS Average
FROM MatchDetails
GROUP BY MatchDetails.PlayerName;
 
What type of field is HowOut? Is it a text field? If so, you should be
able to use one of these expressions.

Abs(Sum(MatchDetails.HowOut <> "No")) <--This will count howout as long
as it is not null or it is not No
or
Count(IIF(MatchDetails.HowOut="No",Null,1)) <<-- this will count HowOut
if it is null
Or
Count(IIF(MatchDetails.HowOut="No",Null,MatchDetails.HowOut)) <--This
will count non-null values that are not "No"
 
Back
Top