Aggregate Functions
AVG
Aggregate functions perform operations on multiple values to create summary results.
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
Analytics. Instead, write the expression manually so that you can control both the numerator and
denominator (x/y).
AVG(numExpr)Where:
numExpr is any expression that evaluates to a numeric
value.
BOTTOMN
Example:
AVG(TIMESTAMPDIFF(SQL_TSI_DAY, "Requisition Dates"."Req. Creation Date", "Requisition Dates"."Latest Filled Date"))
This returns, in Days, the average time between the Req. Creation Date and the Latest Filled Date.
Adding DISTINCT to the formula will give the average (mean) of all distinct values of an expression:
Syntax
AVG(DISTINCT numExpr)
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.
Example:
BOTTOMN("Candidate Counts"."# Submissions", 5)
This returns the 5 locations with the least number of Submissions.
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.
Example:
COUNT("Requisition Collaborator"."Collaborator Name")
This returns the number of Collaborators per Requisition.
Adding DISTINCT to the formula will give the count of all distinct values of an expression:
Syntax
COUNT(DISTINCT expr)
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.
Example:
MAX("Submission Dates"."Hired Date")
This returns the last date/time a candidate was hired.
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.
Example:
MEDIAN(TIMESTAMPDIFF(SQL_TSI_DAY, "Requisition Dates"."Req. Creation Date", "Requisition Dates"."Latest Filled Date"))
This returns, in Days, the median time between the Req. Creation Date and the Latest Filled Date.
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.
Example:
MIN("Submission Dates"."Hired Date")
This returns the first date/time a candidate was hired.
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.
Example:
NTILE("Candidate Counts"."# Submissions", 4)
When Req, Organization is included in Selected Columns, this formula will rank each organization in 4 groups (from 1st quartile to 4th quartile) according to the number of submissions.
PERCENTILE
Similar to NTILE, this function calculates a percent rank for each value satisfying the numeric expression argument but there will be 100 ranks, from 1st percentile to 100th percentile.
Example:
PERCENTILE("Candidate Counts"."# Submissions")
When Req. Organization is included in Selected Columns, this will rank each organization in 100 groups according to the number of submissions.
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.
Example:
RANK("Candidate Counts"."# Submissions")
When Req. Organization is included in Selected Columns, this will rank each organization starting with 1 according to the number of submissions.
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.
Example:
STDDEV("Candidate Counts"."# Submissions")
When Req. Organization is included in Selected Columns, this returns the number of submissions for each organization and the amount of variation of the entire data set (in this case the number of submissions) from the average.
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.
TOPN
Adding DISTINCT to the formula will sum all distinct values of an expression
Syntax
SUM(DISTINCT numExpr)
NOTE: for most numeric fields, Oracle Analytics will automatically sum the values without specifying the function in the code.
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.
Example:
TopN("Candidate Counts"."# Submissions", 5)
When Req. Organization is included in Selected Columns, this returns the top 5 organizations with the highest number of submissions.