Print      Open PDF Version of Online Help

### Running Aggregate Functions

Running aggregate functions are similar to functional aggregates in that they take a set of records as input, but instead of outputting the single aggregate for the entire set of records, they output the aggregate based on records encountered so far.

This section describes the running aggregate functions supported by the Oracle BI Server.

### MAVG

Calculates a moving average (mean) for the last n rows of data in the result set, inclusive of the current row.

Syntax:

MAVG (n_expression, n)

where:

 n_expression Any expression that evaluates to a numerical value. n Any positive integer. Represents the average of the last n rows of data.

The average for the first row is equal to the numeric expression for the first row. The average for the second row is calculated by taking the average of the first two rows of data. The average for the third row is calculated by taking the average of the first three rows of data, and so on until you reach the nth row, where the average is calculated based on the last n rows of data.

### MSUM

This function calculates a moving sum for the last n rows of data, inclusive of the current row.

The sum for the first row is equal to the numeric expression for the first row. The sum for the second row is calculated by taking the sum of the first two rows of data. The sum for the third row is calculated by taking the sum of the first three rows of data, and so on. When the nth row is reached, the sum is calculated based on the last n rows of data.

Syntax:

MSUM (n_expression, n)

Where:

 n_expression Any expression that evaluates to a numerical value. n Any positive integer. Represents the sum of the last n rows of data.

Example:

The following example shows a report that uses the MSUM function.

 MONTH REVENUE 3_MO_SUM JAN 100.00 100.00 FEB 200.00 300.00 MAR 100.00 400.00 APRIL 100.00 400.00 MAY 300.00 500.00 JUNE 400.00 800.00 JULY 500.00 1200.00 AUG 500.00 1400.00 SEPT 500.00 1500.00 OCT 300.00 1300.00 NOV 200.00 1000.00 DEC 100.00 600.00

### RSUM

This function calculates a running sum based on records encountered so far. The sum for the first row is equal to the numeric expression for the first row. The sum for the second row is calculated by taking the sum of the first two rows of data. The sum for the third row is calculated by taking the sum of the first three rows of data, and so on.

Syntax:

RSUM (n_expression)

Where:

 n_expression Any expression that evaluates to a numerical value.

Example:

The following example shows a report that uses the RSUM function.

 MONTH REVENUE RUNNING_SUM JAN 100.00 100.00 FEB 200.00 300.00 MAR 100.00 400.00 APRIL 100.00 500.00 MAY 300.00 800.00 JUNE 400.00 1200.00 JULY 500.00 1700.00 AUG 500.00 2200.00 SEPT 500.00 2700.00 OCT 300.00 3000.00 NOV 200.00 3200.00 DEC 100.00 3300.00

### RCOUNT

This function takes a set of records as input and counts the number of records encountered so far. It resets its value for each group in the query. If a sort order is defined on any column, then this function does not get incremented for adjoining identical values for the sorted column. To avoid this issue, reports should either not contain a sort order on any column or contain sort orders on all columns.

Syntax:

RCOUNT (Expr)

Where:

 Expr An expression of any data type.

Example:

The following example shows a report that uses the RCOUNT function.

 MONTH PROFIT RCOUNT MAY 300.00 2 JUNE 400.00 3 JULY 500.00 4 AUG 500.00 5 SEPT 500.00 6 OCT 300.00 7

### RMAX

This function takes a set of records as input and shows the maximum value based on records encountered so far. The specified data type must be one that can be ordered.

Syntax:

RMAX (expression)

Where:

 expression An expression of any data type. The data type must be one that has an associated sort order.

Example:

The following example shows a report that uses the RMAX function.

 MONTH PROFIT RMAX JAN 100.00 100.00 FEB 200.00 200.00 MAR 100.00 200.00 APRIL 100.00 200.00 MAY 300.00 300.00 JUNE 400.00 400.00 JULY 500.00 500.00 AUG 500.00 500.00 SEPT 500.00 500.00 OCT 300.00 500.00 NOV 200.00 500.00 DEC 100.00 500.00

### RMIN

This function takes a set of records as input and shows the minimum value based on records encountered so far. The specified data type must be one that can be ordered.

Syntax:

RMIN (expression)

Where:

 expression An expression of any data type. The data type must be one that has an associated sort order.

Example:

The following example shows a report that uses the RMIN function.

 MONTH PROFIT RMIN JAN 400.00 400.00 FEB 200.00 200.00 MAR 100.00 100.00 APRIL 100.00 100.00 MAY 300.00 100.00 JUNE 400.00 100.00 JULY 500.00 100.00 AUG 500.00 100.00 SEPT 500.00 100.00 OCT 300.00 100.00 NOV 200.00 100.00 DEC 100.00 100.00

 Published 5/4/2012 Copyright © 2005, 2012, Oracle. All rights reserved. Legal Notices.