1. Visualizing Data and Building Reports in Oracle Analytics Cloud
  2. Reference
  3. Expression Editor Reference
  4. Functions
  5. Aggregate Functions

Aggregate Functions

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

The following list describes the aggregation rules that are available for columns and measure columns. The list also includes functions that you can use when creating calculated items for analyses.

  • Default — Applies the default aggregation rule as in the semantic model or by the original author of the analysis. Not available for calculated items in analyses.

  • Server Determined — Applies the aggregation rule that's determined by the Oracle Analytics (such as the rule that is defined in the semantic model). The aggregation is performed within Oracle Analytics for simple rules such as Sum, Min, and Max. Not available for measure columns in the Layout pane or for calculated items in analyses.

  • Sum — Calculates the sum obtained by adding up all values in the result set. Use this for items that have numeric values.

  • Min — Calculates the minimum value (lowest numeric value) of the rows in the result set. Use this for items that have numeric values.

  • Max — Calculates the maximum value (highest numeric value) of the rows in the result set. Use this for items that have numeric values.

  • Average — Calculates the average (mean) value of an item in the result set. Use this for items that have numeric values. Averages on tables and pivot tables are rounded to the nearest whole number.

  • First — In the result set, selects the first occurrence of the item for measures. For calculated items, selects the first member according to the display in the Selected list. Not available in the Edit Column Formula dialog box.

  • Last — In the result set, selects the last occurrence of the item. For calculated items, selects the last member according to the display in the Selected list. Not available in the Edit Column Formula dialog box.

  • Count — Calculates the number of rows in the result set that have a non-null value for the item. The item is typically a column name, in which case the number of rows with non-null values for that column are returned.

  • Count Distinct — Adds distinct processing to the Count function, which means that each distinct occurrence of the item is counted only once.

  • None — Applies no aggregation. Not available for calculated items in analyses.

  • Report-Based Total (when applicable) — If not selected, specifies that the Oracle Analytics should calculate the total based on the entire result set, before applying any filters to the measures. Not available in the Edit Column Formula dialog box or for calculated items in analyses. Only available for attribute columns.

Function Example Description Syntax

AGGREGATE AT

AGGREGATE(sales AT year)

Aggregates columns based on the level or levels in the data model hierarchy you specify.

  • measure is the name of a measure column.
  • level is the level at which you want to aggregate.

You can optionally specify more than one level. You can't 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 can't write the function as AGGREGATE(yearly_sales AT month) if month is from the same time dimension used as the measure level for yearly_sales.

AGGREGATE(measure AT level [, level1, levelN])
AGGREGATE BY AGGREGATE(sales BY month, region) Aggregates a measure based on one or more dimension columns.

  • measure is the name of a measure column to aggregate.

  • column is the dimension column at which you want to aggregate.
You can aggregate measures based more than one column. 		AGGREGATE(measure BY column [, column1, columnN])

AVG

Avg(Sales)

Calculates the average (mean) of a numeric set of values.

AVG(expr)

AVGDISTINCT

  

Calculates the average (mean) of all distinct values of an expression.

AVG(DISTINCT expr)

BIN

BIN(revenue BY productid, year WHERE productid > 2 INTO 4 BINS RETURNING RANGE_LOW)

Classifies a given numeric expression into a specified number of equal width buckets. The function can return either the bin number or one of the two end points of the bin interval. numeric_expr is the measure or numeric attribute to bin. BY grain_expr1,…, grain_exprN is a list of expressions that define the grain at which the numeric_expr is calculated. BY is required for measure expressions and is optional for attribute expressions. WHERE a filter to apply to the numeric_expr before the numeric values are assigned to bins INTO number_of_bins BINS is the number of bins to return BETWEEN min_value AND max_value is the min and max values used for the end points of the outermost bins RETURNING NUMBER indicates that the return value should be the bin number (1, 2, 3, 4, etc.). This is the default. RETURNING RANGE_LOW indicates the lower value of the bin interval RETURNING RANGE_HIGH indicates the higher value of the bin interval

BIN(numeric_expr [BY grain_expr1, ..., grain_exprN] [WHERE condition] INTO number_of_bins BINS [BETWEEN min_value AND max_value] [RETURNING {NUMBER | RANGE_LOW | RANGE_HIGH}])

BottomN

Ranks the lowest n values of the expression argument from 1 to n, 1 corresponding to the lowest numerical value.

expr is any expression that evaluates to a numerical value. integer is any positive integer. Represents the bottom number of rankings displayed in the result set, 1 being the lowest rank.

BottomN(expr, integer)

COUNT

COUNT(Products)

Determines the number of items with a non-null value.

COUNT(expr)

COUNTDISTINCT

Adds distinct processing to the COUNT function.

expr is any expression.

COUNT(DISTINCT expr)

COUNT*

SELECT COUNT(*) FROM Facts

Counts the number of rows.

COUNT(*)

First

First(Sales)

Selects the first non-null returned value of the expression argument. The First function operates at the most detailed level specified in your explicitly defined dimension.

First([NumericExpression)]

Last

Last(Sales)

Selects the last non-null returned value of the expression.

Last([NumericExpression)]

MAVG

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

expr is any expression that evaluates to a numerical value. integer is any positive integer. Represents the average of the last n rows of data.

MAVG(expr, integer)

MAX

MAX(Revenue)

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

MAX(expr)

MEDIAN

MEDIAN(Sales)

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.

MEDIAN(expr)

MIN

MIN(Revenue)

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

MIN(expr)

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).

expr is any expression that evaluates to a numerical value. numTiles is a positive, nonnull integer that represents the number of tiles.

NTILE(expr, numTiles)

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).

expr is any expression that evaluates to a numerical value.

PERCENTILE(expr)

RANK

RANK(chronological_key, null, year_key_columns)

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'reare assigned the same rank (for example, 1, 1, 1, 4, 5, 5, 7...).

expr is any expression that evaluates to a numerical value.

RANK(expr)

STDDEV

STDDEV(Sales) STDDEV(DISTINCT Sales)

Returns the standard deviation for a set of values. The return type is always a double.

STDDEV(expr)

STDDEV_POP

STDDEV_POP(Sales) STDDEV_POP(DISTINCT Sales)

Returns the standard deviation for a set of values using the computational formula for population variance and standard deviation.

STDDEV_POP([NumericExpression])

SUM

SUM(Revenue)

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

SUM(expr)

SUMDISTINCT

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

expr is any expression that evaluates to a numerical value.

SUM(DISTINCT expr)

TOPN

Ranks the highest n values of the expression argument from 1 to n, 1 corresponding to the highest numerical value.

expr is any expression that evaluates to a numerical value. integer is any positive integer. Represents the top number of rankings displayed in the result set, 1 being the highest rank.

TOPN(expr, integer)

Tips on Using Aggregate Functions

Here're some tips on getting the best results when using aggregate functions in your workbooks.

Tips:

  • Avoid using the First and Last aggregations with a 100% stacked bar chart type. Adding current date to By adds grain to the query, which fetches all rows for the visualization plus the current date. The Last function then returns the last row in that result set, which varies depending on how the data is returned from the source.