Skip to main content

This is documentation for Caché & Ensemble. See the InterSystems IRIS version of this content.

For information on migrating to InterSystems IRIS, see Why Migrate to InterSystems IRIS?

COUNT

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
Feedback