Count conditions

Count conditions on entities

If you set the count condition 'more than N' on an entity in the query, then in the matching record group there will be in total more than 'N' links from all the entities that match that structure entity, to any one entity that matches the structure entity at the other end of the structure link.

Note: If you turn on Distinct (only available in SQL databases), then multiple links between the same entities only contribute 1 to the count. So if, in the example above, an account has 11 transactions all from the same person, then turning on Distinct will exclude it from the results.

Count conditions on links

If the count condition 'more than N', is set on a link in the query, then in the matching record group there will be more than 'N' links between two entities that match the link end structure entities.

For example, if your structure contains:

You might set 'more than 15' on the link to limit the results to telephones that have more than 15 calls between each other. Each telephone in the results is involved in at least 15 calls with one other telephone in the results.

Setting count conditions in a query

1. In the structure area of the query, select the entity or link.

2. Click Set Count on Selected Item.

3. Select Condition to set a count condition and select the type of count:

   • =

     Count must be equal to the number you enter. You can only enter 0 as the count number on entities or links that are not set as the output.

   • <>

     Count must be less than or greater than the number you enter.

   • <

     Count must be less than the number you enter. The count number must be 3 or higher.

     Note: This condition does not include 'equal to 0'. For example, if you had a structure Telephone - Call - Telephone, with less than 3 on one of the telephones, the results would not include telephones that have made no calls. To find telephones that have made no calls, you would must explicitly use the condition equal to 0.

     Because you cannot output the entity with the condition equal to 0, make sure you set the telephone without the condition as the output.

   • >

     Count must be greater than the number you enter.

4. Enter the actual count.

5. If you are using a SQL Server database, turn on Distinct where you want multiple links between the same two entities to only contribute 1 to the count. For details, see the next section.

6. Click OK to confirm the changes.

Distinct counts in SQL Server databases

A distinct count is one that counts the number of different entities linked to a specific entity. In queries based on linked entities, a distinct count helps you determine how many different entities are linked to another.

As with other counts, you can look for relationships where the count is less than, equal to, or more than a number you specify.

For example, you may be interested in bank accounts where one account transfers money into only one other account, regardless of how many transactions take place. In another example, you might be interested in telephones that are used in calls with several other telephones, regardless of how many calls are made in total.

Consider this example:

In the example above, 210 321 2134 is linked to three different telephones, so it has a distinct count of three. All the other telephones have a distinct count of one. The table shows the distinct and non-distinct counts for comparison.

With these counts, you can see the effect of non-distinct and distinct count queries:

• Working with an Access database, you can only specify a non-distinct count condition. A query based on searching for more than two telephones and counting repeated links to the same telephones is effectively a search for more than two telephone calls and would find 210 321 2134, 876 352 0441, and 520 325 2333.

• Working with a SQL Server database, you can specify a distinct count condition. A query based on looking for links to more than two different telephones would find only 210 321 2134.

Note: A non-distinct count is shown with an asterisk, as in Count*.

To create a query involving a distinct count, select the Distinct checkbox.