Types of aggregate calculations
BIRT provides a wide range of functions that perform aggregate calculations. Table 10‑1 describes these functions.
Table 10‑1 Aggregate functions
Aggregate function
Description
AVERAGE
Returns the average (mathematical mean) value in a set of values. For example, if a set contains values 5, 2, 7, and 10, AVERAGE returns 6.
CONCATENATE
Joins the values in a set of values. For example, if a set contains values Belgium, Denmark, and France, CONCATENATE returns BelgiumDenmarkFrance. You can optionally specify a separator to insert between the values, the maximum length of the returned string, and whether to include duplicate values in the returned string.
COUNT
Counts the number of rows. If a set contains values 5, 2, 7, and 10, COUNT returns 4.
COUNTDISTINCT
Counts the number of unique values in a set of values. If a set of values contains values 5, 2, 5, 7, and 10, COUNTDISTINCT returns 4.
FIRST
Returns the first value in a set of values. If a set of values contains values 5, 2, 7, and 10, FIRST returns 5.
IS-BOTTOM-N
Returns a Boolean value that indicates if a value is one of the bottom n values. If a set of values contains 5, 2, 7, and 10, and you specify 2 as the n value, IS‑BOTTOM‑N returns true for values 5 and 2, and false for values 7 and 10.
IS-BOTTOM-N-PERCENT
Returns a Boolean value that indicates if a value is one of the bottom n percent values. If a set of values contains 5, 2, 7, and 10, and you specify 25 (percent) as the n value, IS‑BOTTOM‑N‑PERCENT returns true for 2, and false for 5, 7, and 10.
IS-TOP-N
Returns a Boolean value that indicates if a value is one of the top n values. If a set contains values 5, 2, 7, and 10, and you specify 2 as the n value, IS-TOP-N returns false for 2 and 5, and true for 7 and 10.
IS-TOP-N-PERCENT
Returns a Boolean value that indicates if a value is one of the top n percent values. If a set of values contains 5, 2, 7, and 10, and you specify 25 (percent) as the n value, IS‑TOP‑N‑PERCENT returns false for 5, 2, and 7, and true for 10.
LAST
Returns the last value in a set of values. If a set of values contains values 2, 5, 7, and 10, LAST returns 10.
MAX
Returns the largest value in a set of values. If a set of values contains values 5, 2, 7, and 10, MAX returns 10. For string values, MAX returns the last value alphabetically. For date values, MAX returns the latest date.
MEDIAN
Returns the median, or mid-point, value in a set of values. If a set of values contains values 5, 2, 7, and 10, MEDIAN returns 6.
MIN
Returns the smallest value in a set of values. If a set of values contains values 5, 2, 7, and 10, MIN returns 2. For string values, MIN returns the first value alphabetically. For date values, MIN returns the earliest date.
MODE
Returns the mode, which is the value that occurs most often in a set of values. If a set of values contains values 5, 2, 5, 7, and 10, MODE returns 5.
MOVINGAVE
Returns the moving average for a set of values over a specified interval or number of values. This type of calculation is typically used for analyzing trends of stock prices. For example, you can display the moving average of stock prices over three days. If a set of values contains values 5, 2, 5, 7, and 10, and you specify 3 as the interval, MOVINGAVE returns null, null, 4, 4.66, and 7.33 for each row, respectively.
PERCENTILE
Returns the percentile value in a set of values, given a specified percent rank. For example, you can get the score that represents the 50th percentile of all scores on a test. If a set of values contains 50, 75, 80, 90, and 95, and you specify a percent rank of 0.9 (to get the 90th percentile value), PERCENTILE returns 93.
PERCENTRANK
Returns the rank of a value as a percentage of all the values in a set. The return value ranges from 0 to 1. If a set of values contains 50, 75, 80, 90, and 95, PERCENTRANK returns 0, 0.25, 0.5, 0.75, and 1 for each row, respectively.
PERCENTSUM
Returns the percentage of a total. If a set of values contains 50, 75, 80, 90, and 95, the sum of the values is 390. PERCENTSUM returns 0.128 (50/390), 0.192 (75/390), 0.205 (80/390), 0.231 (90/390), and 0.244 (95/390) for each row, respectively.
QUARTILE
Returns the quartile value in a set of values, given a specified quart (0 ‑ 4). MIN, MEDIAN, and MAX return the same value as QUARTILE when quart is equal to 0, 2, and 4, respectively. If a set of values contains 50, 75, 80, 90, and 95, and you specify a quart of 2, QUARTILE returns 80.
RANK
Returns the rank of a value in a set of values. The rank of a value ranges from 1 to the number of values in the set. If two values are identical, they have the same rank. You can rank values in ascending or descending order. In descending order, the highest value is ranked 1. In ascending order, the lowest value is ranked 1. If a set of values contains 50, 75, 80, 90, and 95, and you specify descending order, RANK returns 5, 4, 3, 2, and 1 for each row, respectively.
RUNNINGCOUNT
Returns the row number, up to a given point, in the report. If a set of values contains 50, 75, 80, 90, and 95, RUNNINGCOUNT returns 1, 2, 3, 4, and 5 for each row, respectively.
RUNNINGSUM
Returns the total, up to a given point, in the report. If a set of values contains 50, 75, 80, 90, and 95, RUNNINGSUM returns 50, 125, 205, 295, and 390 for each row, respectively.
STDDEV
Returns the standard deviation of a set of values. Standard deviation is a statistic that shows how widely values are dispersed from the mean value. If a set of values contains 50, 75, 80, 90, and 95, STDDEV returns 17.536.
SUM
Adds all the values in a set of values. If a set of values contains 50, 75, 80, 90, and 95, SUM returns 390.
VARIANCE
Returns the variance of a set of values. Variance is a statistical measure that expresses how large the differences between the values are. The variance increases as the differences between the numbers increase. If a set of values contains 50, 75, 80, 90, and 95, VARIANCE returns 307.5. If a set of values contains 5, 2, 5, 7, and 10, VARIANCE returns 8.7.
WEIGHTEDAVE
Returns the weighted average value in a set of values, given weights specified in another set of values. In a weighted average, some numbers carry more importance (weight) than others. Grades are often computed using a weighted average. For example:
Score           Weight (counts toward n% of grade)
50                 10
75                 25
80                 15
90                 30
95                 20
Given this set of scores and weights, WEIGHTEDAVE returns 81.75.