Skip to main content

This is documentation for Caché & Ensemble.

For information on converting to InterSystems IRIS, see the InterSystems IRIS Adoption Guide and the InterSystems IRIS In-Place Conversion Guide, both available on the WRC Distributions page (login required).

# 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.

## Returned Type

This function returns a number.

## Syntax and Details

```AGGREGATE(set_expression, optional_numeric_expression)
```

Where:

• set_expression is an expression that evaluates to a set, typically a set of members or tuples.

• optional_numeric_expression is a numeric-valued expression that the function evaluates for each set element.

Typically, this expression has the form [MEASURES].[measure_name]

If you do not specify a numeric expression, DeepSee 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, DeepSee 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.

## 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.

Feedback