Aggregate Functions

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

Functions

Avg
AvgDistinct
BottomN
Count
CountDistinct
Count*
Mavg
Max
Median
Min
NTile
Percentile
Rank
StdDev
StdDev_Pop
Sum
SumDistinct
TopN

---

Avg

Calculates the average (mean) value of an expression in a result set. Must take a numeric expression as its argument.

Syntax

Avg(numExpr)

Where:

numExpr

Any expression that evaluates to a numerical value.

---

AvgDistinct

Calculates the average (mean) of all distinct values of an expression. Must take a numeric expression as its argument.

Syntax

Avg(DISTINCT numExpr)

Where:

numExpr

Any expression that evaluates to a numerical value.

---

BottomN

Ranks the lowest n values of the expression argument from 1 to n, 1 corresponding to the lowest numerical value. The BOTTOMN function operates on the values returned in the result set. A request can contain only one BOTTOMN expression.

Syntax

BottomN(Expr, integer)

Where:

Expr

Any expression that evaluates to a numerical value.

integer

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

---

Count

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(numExpr)

Where:

numExpr

Any expression that evaluates to a numerical value.

---

CountDistinct

Adds distinct processing to the count function.

Syntax

Count(DISTINCT numExpr)

Where:

numExpr

Any expression that evaluates to a numerical value.

---

Count *

Counts the number of rows.

Syntax

Count(*)

Example

Suppose a table named Facts contains 200,000,000 rows. The sample request would return the result shown.

SELECT Count(*) FROM dim_tables

Result

5000

---

Mavg

Calculates a moving average (mean) for the last n rows of data, inclusive of the current row.
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. When the nth row is reached, the average is calculated based on the last n rows of data.

Syntax

MAVG (numExpr, integer)

Where:

numExpr

Any expression that evaluates to a numerical value.

integer

Any positive integer. Represents the average of the last n rows of data.

---

Max

Calculates the maximum value (highest numeric value) of the rows satisfying the numeric expression argument.

Syntax

Max(numExpr)

Where:

numExpr

Any expression that evaluates to a numerical value.

---

Median

Calculates the median (middle) value of the rows satisfying the numeric expression argument. Where there are an even number of rows, the median is the mean of the two middle rows.

Syntax

Median(numExpr)

Where:

numExpr

Any expression that evaluates to a numerical value.

---

NTile

Determines the rank of a value in terms of a user-specified range. It returns integers to represent any range of ranks. 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

Any expression that evaluates to a numerical value.

numTiles

Any positive, nonnull integer.

---

Min

Calculates the minimum value (lowest numeric value) of the rows satisfying the numeric expression argument.

Syntax

Min(numExpr)

Where:

numExpr

Any expression that evaluates to a numerical value.

---

Percentile

Calculates a percentile rank for each value satisfying the numeric expression argument. The percentile rank ranges are between 0 (0th percentile) to 1 (100th percentile). The percentile is calculated based on the values in the result set.

Syntax

Percentile(Expr)

Where:

Expr

Any expression that evaluates to a numerical value.

---

Rank

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(Expr)

Where:

Expr

Any expression that evaluates to a numerical value.

---

StdDev

Returns the standard deviation for a set of values. StdDev_Samp is a synonym for StdDev.

Syntax

StdDev([ALL | DISTINCT] numExpr)
StdDev_Samp([ALL | DISTINCT] numExpr)

Where:

numExpr

Any expression that evaluates to a numerical value.

ALL

The standard deviation is calculated for all data in the set.

DISTINCT

All duplicates are ignored in the calculation.

---

StdDev_Pop

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

Any expression that evaluates to a numerical value.

ALL

The standard deviation is calculated for all data in the set.

DISTINCT

All duplicates are ignored in the calculation.

---

Sum

Calculates the sum obtained by adding up all values satisfying the numeric expression argument.

Syntax

Sum(numExpr)

Where:

numExpr

Any expression that evaluates to a numerical value.

---

SumDistinct

Calculates the sum obtained by adding all of the distinct values satisfying the numeric expression argument.

Syntax

Sum(DISTINCT numExpr)

Where:

numExpr

Any expression that evaluates to a numerical value.

---

TopN

Ranks the highest n values of the expression argument from 1 to n, 1 corresponding to the highest numerical value. The TOPN function operates on the values returned in the result set. A request can contain only one TOPN expression.

Syntax

TopN(Expr, integer)

Where:

Expr

Any expression that evaluates to a numerical value.

integer

Any positive integer. Represents the bottom number of rankings displayed in the result set, 1 being the highest rank.