DeepSee MDX Reference
COUNT
[Home] [Back] [Next]
InterSystems: The power behind what matters   
Class Reference   
Search:    

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