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

Returns the aggregate value for a given measure (or of the current measure), across all elements of a set, according to the aggregation logic of the measure.
Returned Type
This function returns a number.
Syntax and Details
AGGREGATE(set_expression, optional_numeric_expression)
Where:
The function evaluates the numeric value for each element of the set and returns the aggregate value of those values.
Example
First, the following query shows values of three measures for the members of the aged.decade level:
SELECT {MEASURES.[%COUNT],MEASURES.[encounter count],MEASURES.[avg test score]} ON 0, 
birthd.decade.MEMBERS ON 1 FROM patients
                     Patient Count    Encounter Count     Avg Test Score
 1 1910s                         80              5,359              75.17
 2 1920s                        227             12,910              74.20
 3 1930s                        567             33,211              74.67
 4 1940s                        724             38,420              73.39
 5 1950s                      1,079             46,883              73.72
 6 1960s                      1,475             57,814              74.16
 7 1970s                      1,549             49,794              74.35
 8 1980s                      1,333             35,919              74.13
 9 1990s                      1,426             29,219              74.79
10 2000s                      1,406             20,072              74.95
11 2010s                        134              1,346              73.55
Next, the following query uses AGGREGATE to find the aggregated values for these measures, across this set of members:
SELECT {MEASURES.[%COUNT],MEASURES.[encounter count],MEASURES.[avg test score]} ON 0, 
AGGREGATE(birthd.decade.MEMBERS) ON 1 FROM patients
                     Patient Count    Encounter Count     Avg Test Score
AGGREGATE                    10,000            330,947              74.28
The following query uses the second argument of the AGGREGATE function:
SELECT AGGREGATE(birthd.decade.MEMBERS, MEASURES.[%COUNT]) ON 0 FROM patients
                                 AGGREGATE
                                     10,000
For additional, similar examples, see AVG. Also see examples in Defining Calculated Members in Defining DeepSee Models.
See Also