COUNT (MDX)

Returns the count of elements in the given set.

Returned Type

This function returns a number.

Syntax and Details

COUNT(set_expression)

Or:

COUNT(set_expression,EXCLUDEEMPTY)

set_expression is an expression that evaluates to a set.

By default, COUNT considers any empty elements and counts them along with the non-empty elements. If you use the EXCLUDEEMPTY keyword, this function returns the number of non-empty elements.

Example

For example, the following query counts the members of the Home City level:

SELECT COUNT(homed.city.MEMBERS) ON 0 FROM patients
 
                                     COUNT
Results                                  9

The next examples demonstrate the EXCLUDEEMPTY keyword. First, consider the following query:

SELECT aged.[age group].MEMBERS ON 0, diagd.MEMBERS ON 1 FROM patients WHERE MEASURES.[%COUNT]

                           0 to 29           30 to 59                60+
1 None                        3,839              3,615                971
2 asthma                        308                282                113
3 CHD                             1                 93                229
4 diabetes                       30                246                228
5 osteoporosis                    *                  *                200

The following query counts the number of members of the Diagnoses level:

WITH SET myset AS 'diagd.MEMBERS' 
SELECT COUNT(myset) ON 0 FROM patients
                                     COUNT
All Patients                              5

The following query counts the number of members of the Diagnoses level and uses the WHERE clause to get only patients in the age group 0 to 29:

WITH SET myset AS 'diagd.MEMBERS' 
SELECT COUNT(myset) ON 0 FROM patients WHERE aged.[0 to 29]

                                     COUNT
                                          5

As you can see, although the query uses the WHERE clause, the COUNT function returns the same value as before; this is because COUNT considers empty elements by default.

The next query is a variation of the preceding but uses EXCLUDEEMPTY:

WITH SET myset AS 'diagd.MEMBERS' SELECT COUNT(myset,EXCLUDEEMPTY) ON 0 FROM patients WHERE aged.[0 to 29]

                                     COUNT
                                          4

For another example, you can use COUNT with a set of scalar items, rather than the more common set of members:

WITH SET test AS '{"item 1","item 2",23}' 
SELECT COUNT(test) ON 0 FROM patients                                     COUNT
All Patients                              3

COUSIN (MDX)

CLOSINGPERIOD (MDX)