Skip to main content

SUM (MDX)

Returns the sum of a given expression (or of the current measure), across all elements of a set.

Returned Type

This function returns a number.

Syntax and Details

SUM(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, 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 sum 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 shows the sums for these measures for the members of this level:

SELECT {MEASURES.[%COUNT],MEASURES.[encounter count],MEASURES.[avg test score]} ON 0, 
SUM(birthd.decade.MEMBERS) ON 1 FROM patients
                     Patient Count    Encounter Count     Avg Test Score
SUM                          10,000            330,947             817.08

Here, each value is the sum of the values in a column in the preceding query. For example, the Patient Count value is the sum of the Patient Count values in the preceding query. The Avg Test Score value is the sum of the average test scores and is probably not a useful value.

For another example, we use the second argument for SUM:

SELECT SUM(birthd.decade.MEMBERS, MEASURES.[%COUNT]) ON 0 FROM patients
                                       SUM
                                     10,000

For additional, similar examples, see AVG.

See Also

FeedbackOpens in a new tab