| Print Open PDF Version of Online Help

#### Aggregate Functions Aggregate functions perform work on multiple values to create summary results. The aggregate functions cannot be used to form a nested aggregation in expressions on columns that have a default aggregation rule predefined by Oracle CRM On Demand. ### Avg Calculates the average (mean) value of an expression in a result set. Has to take a numeric expression as its argument. Syntax: AVG (n_expression) where:
n_expression | Any expression that evaluates to a numerical value.
### AvgDistinct Calculates the average (mean) of all distinct values of an expression. Has to take a numeric expression as its argument. Syntax: AVG (DISTINCT n_expression) where:
n_expression | 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. Syntax: BOTTOMN (n_expression, n) where:
n_expression | Any expression that evaluates to a numerical value.
n | Any positive integer. Represents the bottom number of rankings displayed in the result set, 1 being the lowest rank.
NOTE: A query can contain only one BOTTOMN expression. ### ### 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 (expression) where:
expression | Any expression.
### CountDistinct Adds distinct processing to the COUNT function. Syntax: COUNT (DISTINCT expression) where:
expression | Any expression.
### Count (*) (CountStar) Counts the number of rows. Syntax: COUNT(*) For example, if a table named Facts contained 200,000,000 rows, the following query would return the following results: SELECT COUNT(*) FROM Facts COUNT(*) 200000000 ### 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 (expression) where:
expression | Any expression.
### Median 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 (n_expression) where:
n_expression | Any expression that evaluates to a numerical value.
### Min Calculates the minimum value (lowest numeric value) of the rows satisfying the numeric expression argument. Syntax: MIN (expression) where:
expression | Any expression.
### NTile The NTILE 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 a number of tiles where there are roughly an equal number of values in each tile. Syntax: NTILE (n_expression, n) where:
n_expression | Any expression that evaluates to a numerical value.
n | A positive, nonnull integer that represents the number of tiles.
If the n_expression argument is not NULL, the function returns an integer that represents a rank within the reported range. NTile with n=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 Server percentile function, which conforms to what is called percent rank in SQL 92 and returns values from 0 to 1. ### Percentile Calculates a percent rank for each value satisfying the numeric expression argument. The percent rank ranges are from 0 (1st percentile) to 1 (100th percentile), inclusive. The PERCENTILE function calculates the percentile based on the values in the result set of the query. Syntax: PERCENTILE (n_expression) where:
n_expression | 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 function calculates the rank based on the values in the result set of the query. Syntax: RANK (n_expression) where:
n_expression | Any expression that evaluates to a numerical value.
### StdDev The STDDEV function returns the standard deviation for a set of values. The return type is always a double. Syntax: STDDEV([ALL | DISTINCT] n_expression) where:
n_expression | Any expression that evaluates to a numerical 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. There are two other functions that are related to STDDEV: STDDEV_POP([ALL | DISTINCT] n_expression) STDDEV_SAMP([ALL | DISTINCT] n_expression) STDDEV and STDDEV_SAMP are synonyms. ### ### 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.
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. ### Sum Calculates the sum obtained by adding up all values satisfying the numeric expression argument. Syntax: SUM (n_expression) where:
n_expression | 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 n_expression) where:
n_expression | 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. Syntax: TOPN (n_expression, n) where:
n_expression | Any expression that evaluates to a numerical value.
n | Any positive integer. Represents the top number of rankings displayed in the result set, 1 being the highest rank.
A query can contain only one TOPN expression.

Published August 2022 | Copyright © 2005, 2022, Oracle and/or its affiliates. Legal Notices.