#
Aggregate Functions

Aggregate functions perform operations on multiple values to create summary results.

The aggregate functions cannot be used to form nested aggregation in expressions on logical columns that have a default aggregation rule defined in the Aggregation tab of the Logical Column dialog. To specify nested aggregation, you must define a column with a default aggregation rule and then request the aggregation of the column in a SQL statement.

Aggregate functions include:

·
AGGREGATE
AT

·
AVG

·
AVGDISTINCT

·
BOTTOMN

·
COUNT

·
COUNTDISTINCT

·
COUNT(*)

·
FIRST

·
GROUPBYCOLUMN

·
GROUPBYLEVEL

·
LAST

·
MAX

·
MEDIAN

·
MIN

·
NTILE

·
PERCENTILE

·
RANK

·
STDDEV

·
STDDEV_POP

·
SUM

·
SUMDISTINCT

·
TOPN

##
*AGGREGATE
AT*

This function aggregates columns based on the level or levels you specify. Using

`AGGREGATE AT`

guarantees that the aggregate for the
measure always occurs at the levels specified after the keyword `AT`

, regardless of the `WHERE`

clause.Syntax

`AGGREGATE(expr AT level [, level1, levelN])`

Where:

expr is any expression that references at least one measure column

level is the level at which you want to aggregate. You can optionally specify multiple levels.

You cannot specify a level from a dimension that contains levels that are being used as the measure level for the measure you specified in the first argument. For example, you cannot write the function as

`AGGREGATE(yearly_sales AT month)`

because "month" is from the same time
dimension that is being used as the measure level for "yearly_sales."Example

The following example shows the

`AGGREGATE AT`

function and example results:`SELECT month, year, AGGREGATE(sales AT Year)`

`FROM timeseriestestingWHERE year = 1994 AND month = 12`

Result:

`Month Year AGGREGATE AT year12 1994 7396Row count: 1`

Because the

`AGGREGATE AT`

operator is always executed before the
predicates, it always returns the correct total for the time level specified
after the keyword `AT`

.##
*AVG*

This function calculates the average (mean) value of an expression in a result set. It must take a numeric expression as its argument.

Note that the denominator of

`AVG`

is the number of rows aggregated. For
this reason, it is usually a mistake to use `AVG(`

x`)`

in a calculation in Oracle Business Intelligence. Instead, write the
expression manually so that you can control both the numerator and denominator
(x/y).Syntax

`AVG(numExpr)`

Where:

numExpr is any expression that evaluates to a numeric value.

##
*AVGDISTINCT*

This function calculates the average (mean) of all distinct values of an expression. It must take a numeric expression as its argument.

Syntax

`AVG(DISTINCT numExpr)`

Where:

numExpr is any expression that evaluates to a numeric value.

##
*BOTTOMN*

This function ranks the lowest n values of the expression argument from 1 to n, 1 corresponding to the lowest numeric value. The

`BOTTOMN`

function operates on the values returned
in the result set. A request can contain only one `BOTTOMN`

expression.Syntax

`BOTTOMN(numExpr, integer)`

Where:

numExpr is any expression that evaluates to a numeric value.

integer is any positive integer. Represents the bottom number of rankings displayed in the result set, 1 being the lowest rank.

##
*COUNT*

This function calculates the number of rows having a nonnull value for the expression. The expression is typically a column name, in which case the number of rows with nonnull values for that column is returned.

Syntax:

`COUNT(expr)`

Where:

expr is any expression.

##
*COUNTDISTINCT*

This function adds distinct processing to the

`COUNT`

function.Syntax

`COUNT(DISTINCT expr)`

Where:

expr is any expression.

##
*COUNT(*)*

This function counts the number of rows.

Syntax

`COUNT(*)`

Example

For example, if a table named Facts contained 200,000,000 rows, the sample request would return the results shown:

`SELECT COUNT(*) FROM Facts`

Result:

`200000000`

##
*FIRST*

This function selects the first returned value of the expression argument. For example, the

`FIRST`

function can calculate the value of the
first day of the year.The

`FIRST`

function is limited to defining
dimension-specific aggregation rules in a repository. You cannot use it in SQL
statements.The

`FIRST`

function operates at the most detailed level
specified in your explicitly defined dimension. For example, if you have a time
dimension defined with hierarchy levels day, month, and year, the `FIRST`

function returns the first day in each
level.You should not use the

`FIRST`

function as the first dimension-specific
aggregate rule. It might cause queries to bring back large numbers of rows for
processing in the Oracle BI Server, causing poor performance.When a measure is based on dimensions, and data is dense, the Oracle BI Server optimizes the SQL statements sent to the database to improve performance.

Note that you cannot nest

`PERIODROLLING`

, `FIRST`

, and `LAST`

functions.Syntax

`FIRST(expr)`

Where:

expr is any expression that references at least one measure column.

`FIRST(sales)`

##
*GROUPBYCOLUMN*

For use in setting up aggregate navigation. It specifies the logical columns that define the level of the aggregate data existing in a physical aggregate table.

For example, if an aggregate table contains data grouped by store and by month, specify the following syntax in the content filter (General tab of Logical Source dialog):

`GROUPBYCOLUMN(STORE, MONTH)`

The

`GROUPBYCOLUMN`

function is only for use in configuring a
repository. You cannot use it to form SQL statements.##
*GROUPBYLEVEL*

For use in setting up aggregate navigation. It specifies the dimension levels that define the level of the aggregate data existing in a physical aggregate table.

For example, if an aggregate table contains data at the store and month levels, and if you have defined dimensions (Geography and Customers) containing these levels, specify the following syntax in the content filter (General tab of Logical Source dialog):

`GROUPBYLEVEL(GEOGRAPHY.STORE, CUSTOMERS.MONTH)`

The

`GROUPBYLEVEL`

function is only for use in configuring a
repository. You cannot use it to form SQL statements.##
*LAST*

This function selects the last returned value of the expression. For example, the

`LAST`

function can calculate the value of the last day
of the year.The

`FIRST`

function is limited to defining
dimension-specific aggregation rules in a repository. You cannot use it in SQL
statements.The

`LAST`

function operates at the most detailed level
specified in your explicitly defined dimension. For example, if you have a time
dimension defined with hierarchy levels day, month, and year, the `LAST`

function returns the last day in each
level.You should not use the

`LAST`

function as the first dimension-specific
aggregate rule. It might cause queries to bring back large numbers of rows for
processing in the Oracle BI Server, causing poor performance.When a measure is based on dimensions, and data is dense, the Oracle BI Server optimizes the SQL statements sent to the database to improve performance.

Note that you cannot nest

`PERIODROLLING`

, `FIRST`

, and `LAST`

functions.Syntax

`LAST(expr)`

Where:

expr is any expression that references at least one measure column.

`LAST(sales)`

##
*MAX*

This function calculates the maximum value (highest numeric value) of the rows satisfying the numeric expression argument.

Syntax

`MAX(numExpr)`

Where:

numExpr is any expression that evaluates to a numeric value.

The

`MAX`

function resets its values for each group in the
query according to specific rules.##
*MEDIAN*

This function calculates the median (middle) value of the rows satisfying the numeric expression argument. When there are an even number of rows, the median is the mean of the two middle rows. This function always returns a double.

Syntax

`MEDIAN(numExpr)`

Where:

numExpr is any expression that evaluates to a numeric value.

The

`MEDIAN`

function resets its values for each group
in the query according to specific rules.##
*MIN*

This function calculates the minimum value (lowest numeric value) of the rows satisfying the numeric expression argument.

Syntax

`MIN(numExpr)`

Where:

numExpr is any expression that evaluates to a numeric value.

The

`MIN`

function resets its values for each group in the
query according to specific rules.##
*NTILE*

This function determines the rank of a value in terms of a user-specified range. It returns integers to represent any range of ranks. In other words, the resulting sorted data set is broken into several tiles where there are roughly an equal number of values in each tile.

`NTile`

with numTiles = 100
returns what is commonly called the "percentile" (with numbers
ranging from 1 to 100, with 100 representing the high end of the sort). This
value is different from the results of the Oracle BI `PERCENTILE`

function, which conforms to what is
called "percent rank" in SQL 92 and returns values from 0 to 1.Syntax

`NTILE(numExpr, numTiles)`

Where:

numExpr is any expression that evaluates to a numeric value.

numTiles is a positive, nonnull integer that represents the number of tiles.

If the numExpr argument is not null, the function returns an integer that represents a rank within the requested range.

##
*PERCENTILE*

This function calculates a percent rank for each value satisfying the numeric expression argument. The percentile rank ranges are from 0 (1st percentile) to 1 (100th percentile), inclusive.

The percentile is calculated based on the values in the result set.

Syntax

`PERCENTILE(numExpr)`

Where:

numExpr is any expression that evaluates to a numeric value.

The

`PERCENTILE`

function resets its values for each group
in the query according to specific rules.##
*RANK*

This function calculates the rank for each value satisfying the numeric expression argument. The highest number is assigned a rank of 1, and each successive rank is assigned the next consecutive integer (2, 3, 4,...). If certain values are equal, they are assigned the same rank (for example, 1, 1, 1, 4, 5, 5, 7...).

The rank is calculated based on the values in the result set.

Syntax

`RANK(numExpr)`

Where:

numExpr is any expression that evaluates to a numeric value.

The

`RANK`

function resets its values for each group in the
query according to specific rules.##
*STDDEV*

This function returns the standard deviation for a set of values. The return type is always a double.

`STDEV_SAMP`

is a synonym for `STDDEV`

.Syntax

`STDDEV([ALL | DISTINCT] numExpr)`

Where:

numExpr is any expression that evaluates to a numeric value.

If

`ALL`

is specified, the standard deviation is
calculated for all data in the set.If

`DISTINCT`

is specified, all duplicates are ignored
in the calculation.If nothing is specified (the default), all data is considered.

The

`STDDEV`

function resets its values for each group
in the query according to specific rules. ##
*STDDEV_POP*

This function returns the standard deviation for a set of values using the computational formula for population variance and standard deviation.

Syntax

`STDDEV_POP([ALL | DISTINCT] numExpr)`

Where:

numExpr is any expression that evaluates to a numeric value.

If

`ALL`

is specified, the standard deviation is
calculated for all data in the set.If

`DISTINCT`

is specified, all duplicates are ignored
in the calculation.If nothing is specified (the default), all data is considered.

##
*SUM*

This function calculates the sum obtained by adding up all values satisfying the numeric expression argument.

Syntax

`SUM(numExpr)`

Where:

numExpr is any expression that evaluates to a numeric value.

The

`SUM`

function resets its values for each group in the
query according to specific rules. ##
*SUMDISTINCT*

This function calculates the sum obtained by adding all of the distinct values satisfying the numeric expression argument.

Syntax

`SUM(DISTINCT numExpr)`

Where:

numExpr is any expression that evaluates to a numeric value.

##
*TOPN*

This function ranks the highest n values of the expression argument from 1 to n, 1 corresponding to the highest numeric value. The

`TOPN`

function operates on the values returned
in the result set. A request can contain only one `TOPN`

expression.Syntax

`TOPN(numExpr, integer)`

Where:

numExpr is any expression that evaluates to a numeric value.

integer is any positive integer. Represents the top number of rankings displayed in the result set, 1 being the highest rank.

The

`TOPN`

function resets its values for each group in the
query according to specific rules.
Reference :

otn.oracle.com