Jump to content

Counting with two specific values


This topic is 3097 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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?

Link to comment
Share on other sites

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 ; "" ; "" ; ""
)

 

Link to comment
Share on other sites

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 ).

Link to comment
Share on other sites

This topic is 3097 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.