Counting with two specific values

[madman411]

I'm trying to count related records (within the same table) where two values are met. The first value is a serial number (Serial), the second value is a "1" (flag). 

For example:

Record 1 / Serial: 12345 / Flag: 1
Record 2 / Serial: 12345 / Flag: 1
Record 3 / Serial: 12345 / Flag : (empty)
Record 4 / Serial: 6789 / Flag: 1
Record 5 / Serial: 6789 / Flag: (empty)

Records with serial number 12345 should have a count of 2. Records with serial number 6789 should have a count of 1. To quickly explain, this is for conditional formatting. I have a portal with groups of records that fall under a "header" row. If one or more items within a group (joined by Serial) have a 1 in the flag field then the header needs to be a certain color for that specific group.

I think I'm over complicating this but I haven't found a solution that works. Would this be better calculated as an SQL statement?

[madman411]

Ok, using the following SQL statement I achieved what I was looking for, but it's extremely slow. Is there a way to make this statement a little lighter on the computer?

ExecuteSQL ("SELECT Count (*) 

FROM Items 
WHERE Flag = 1 
and Serial 
IN ( ? , ? , ? , ? )
" ; "" ; "" ; Serial ; "" ; "" ; ""
)

 

[eos]

What you want is probably

ExecuteSQL ( "
  SELECT COUNT (*) 
  FROM Items 
  WHERE 
    Flag = 1 AND 
    Serial = ?
  " ; "" ; "" ; Items::Serial 
)

or create a self-join for Items by Serial, then use Count ( Items_selfJoinBySerial::Flag ).

[madman411]

or create a self-join for Items by Serial, then use Count ( Items_selfJoinBySerial::Flag ).

Just what I thought - totally overthinking it.

 Thanks eos!