This is documentation for Caché & Ensemble. See the InterSystems IRIS version of this content.Opens in a new window

For information on migrating to InterSystems IRISOpens in a new window, see the InterSystems IRIS Migration Guide and Migrating to InterSystems IRIS, both available on the WRC Distributions pageOpens in a new window (login required).

# TOPSUM

Sorts a set and returns a subset from its higher-valued end, given a cutoff value that is applied to a total across elements.

## Returned Type

This function returns a set of members or tuples, depending on the set used.

## Syntax and Details

```TOPSUM(set_expression, cutoff_value, ordering_expression)
```
• set_expression is an expression that evaluates to a set of members or tuples.

• cutoff_value is a numeric literal.

The function uses this argument to determine the cutoff value for elements to return in the subset.

For all elements in the returned subset, the sum of the values of ordering_expression will be less than or equal to cutoff_value.

• ordering_expression is a numeric-valued expression that determines the order of the set members.

The function evaluates this expression for each element of the set and sorts the elements of the set in descending order according to this value. Any hierarchies are ignored.

## Example

First consider an example in which the cutoff value is high enough to include all members:

```SELECT MEASURES.[%COUNT] ON 0,
TOPSUM(birthd.decade.MEMBERS, 10000, MEASURES.[%COUNT]) ON 1 FROM patients
Patient Count
1 1970s                              1,520
2 1960s                              1,500
3 2000s                              1,433
4 1990s                              1,413
5 1980s                              1,400
6 1950s                              1,030
7 1940s                                683
8 1930s                                572
9 1920s                                223
10 2010s                                155
11 1910s                                 71```

Now consider a variation in which the cutoff value is set to 2500:

```SELECT MEASURES.[%COUNT] ON 0,
TOPSUM(birthd.decade.MEMBERS, 2500, MEASURES.[%COUNT]) ON 1 FROM patients
Patient Count
1970s                                 1,520```