COUNTDISTINCTMEMBERS function

The COUNTDISTINCTMEMBERS function counts the number of elements in a set that has the union of all its values.

COUNTDISTINCTMEMBERS is functionally equivalent to this statement: 
CARDINALITY(SET_UNIONS(multi-assign-attribute))

That is, COUNTDISTINCTMEMBERS first constructs a set that is the union of all the values from a multi-assign attribute and then returns the number of elements in that set.

COUNTDISTINCTMEMBERS syntax

The syntax of the COUNTDISTINCTMEMBERS function is: 
COUNTDISTINCTMEMBERS(<multi-assign-attribute>)
where multi-assign-attribute is a multi-assign attribute.

COUNTDISTINCTMEMBERS example

Assume the following nine records that are of WineType=Red (where WineType is a single-assign attribute). Each record includes one or two assignments for the multi-assign Body attribute: 
Body                WineID
--------------------------
{ Silky, Tannins }    3
{ Robust, Tannins }   4
{ Silky, Tannins }    5
{ Robust }            6
{ Robust }            8
{ Silky, Tannins }    9
{ Silky, Tannins }   12
{ Silky, Tannins }   16
{ Silky, Tannins }   18
--------------------------
The following statement returns the number of different values for the Body attribute in the WineType=Red records: 
RETURN Result AS
SELECT COUNTDISTINCTMEMBERS(Body) AS Total
FROM WineState
WHERE WineType = 'Red'
GROUP BY WineType
The statement result is: 
Total=3, WineType=Red
For this group, the value of Total is 3 because there are three non-empty sets with unique values for the Body attribute:
  • One set for Records 3, 5, 9, 12, 16, and 18, each of which has the "Silky" and "Tannins" assignments for Body.
  • One set for Records 6 and 8, each of which has the "Robust" assignment for Body.
  • One set for Record 4, which has the "Robust" and "Tannins" assignments for Body.

Thus, there are three sets of distinct values for the Body attribute, when grouped by the WineType attribute.