### Title

Calulate number of unique entries by a certain attribute?

### Post

Hi,

I am new to FM Pro and have little experience with the scripts. Can anyone show me how to obtain the number of unique entries in a dataset by a certain attributes?

For example, in the following table (with heading), how can I calculate the number of unique entries in B, by different A data (the answer is for A=1, there are 2 unique B entries; for A=2, there are 3 unique B entries; for A=3, there is 1 unique B entry)?

A B

1 7

1 8

1 7

2 6

2 7

2 8

3 6

3 6

Thanks.

-Jingjing

If you are familiar with SQL, you can use SELECT with the DISTINCT keyword to get such counts. A new way to count unique values in FileMaker 12

And while this may seem an odd way to go about it, you can set up a conditional value list based on a relationship matching the value of A to all other records with the same value of A (A self join if both columns of your data are in the same table). ValueCount ( ValueListITems ( Get ( FileName ) ; "ConditionalValueLIstNameHere" ) ) will then return the number of unique values of B for the current record's value in A.

And for those truly interested in esoteric ways to use FileMaker (In other words, if you thought the last method was odd...) there's a method that's been around for years that predates the ExecuteSQL function called "Sum the reciprocal": How to count the number of unique occurences in field.