Does each row in you example data represent a different record?
If each row is a different record, do you want a count of all the records in the table where the value is zero?
Or a count for each record where the value is zero for a subset of the total records? (And if so, how do you define that subset?)
Are 0,1,2 the only possible values for that field? (Could the field ever be empty instead of having one of these three values?
Are you using FileMaker 12?
Yes each row represents a different record.
I want a count where the value is zero for a subset of the total records. That subset is defined by another field, in my example, the field is A,B, or C. that's why the A has a count of 0 0s, B has a count of 2 0s, and C has a count of 1 0s.
Yes 0,1, 2 are the only possible values, but I was thinking maybe I should account for empty's just in case. However, it shouldn't matter because I only care if the field contains a 0, right? Anything else can just be ignored including empty.
Yes I am using filemaker 12.
In some expressions, a field with 0 and a field that's empty evaluate the same and other methods treat them as different values so knowing whether or not you need to descriminate between 0 and empty is a key detail.
Without using Execute SQL:
You could set up a calculation field, cZeroFlag in this table:
Field 1 = 0
Select Number as the return type and leave the "Do not evaluate if all referenced fields are empty" check box selected so you can ignore cases where Field 1 is empty.
Then you can define a self join relationship that uses Field 2 as the match field. And then a calculation field with this expression:
Count ( YourTable2::cZeroFlag )
Using Execute SQL:
An expression similar to this:
ExecuteSQL ( "SELECT Count ( * ) FROM \"YourTableName\" WHERE \"YourTableName.Field1\" = 0 and \"YourTableName.Field2\" = ?" ; "" ; "" ; YourTable::Field2 )
should also produce the same count without the extra fields and relationship.