InterSystems MDX Reference
AGGREGATE


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.
This function returns a
number.
AGGREGATE(set_expression, optional_numeric_expression)


If you do not specify a numeric expression, the system uses the measure used by the current result cell. For example, this might be the measure used on the 0 axis or the measure specified in the WHERE clause, if any. If the query itself does not specify a measure, the system instead uses %COUNT, which counts records in the fact table.
The function evaluates the numeric value for each element of the set and returns the aggregate value of those values.
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