I have a query that is getting a count of all records of a certain field from another query. I am trying to only count the number of records where that field is equal to a certain value.
Example :
[OnTime]
1
1
1
0
1
0
0
1
Count(8 records for that table)
Below is the sql that gets the count
SELECT Records.SpotNumber, Count(qGetDatesForAvg.OnTime) AS CountOfOnTime
FROM Records INNER JOIN qGetDatesForAvg ON Records.SpotNumber = qGetDatesForAvg.SpotNumber
GROUP BY Records.SpotNumber;
If I only want to count the records where qGetDatesForAvg.OnTime=1 to get Count(5 records for that table where OnTime=1), how would I do that. I can get the Count of all records from that table, but I cannot figure out how to set a criteria to only get the count where the record values = certain value. Any ideas?
Example :
[OnTime]
1
1
1
0
1
0
0
1
Count(8 records for that table)
Below is the sql that gets the count
SELECT Records.SpotNumber, Count(qGetDatesForAvg.OnTime) AS CountOfOnTime
FROM Records INNER JOIN qGetDatesForAvg ON Records.SpotNumber = qGetDatesForAvg.SpotNumber
GROUP BY Records.SpotNumber;
If I only want to count the records where qGetDatesForAvg.OnTime=1 to get Count(5 records for that table where OnTime=1), how would I do that. I can get the Count of all records from that table, but I cannot figure out how to set a criteria to only get the count where the record values = certain value. Any ideas?
Last edited: