TALLY

The TALLY function counts the number of values of a dimension that correspond to each value of one or more related dimensions.

Return Value

INTEGER

Syntax

TALLY(dimension [related-dimensions])

Parameters

dimension: A dimension whose values are to be counted. When you specify related-dimensions, TALLY counts the number of values of dimension that correspond to each value of a single related dimension, or to each combination of values of two or more related dimensions. When you do not specify related-dimensions, TALLY counts the number of values in the dimension. Only values in the current status of dimension are counted.
related-dimensions: One or more related dimensions for the results. These must be related to dimension. Alternatively, you can specify the name of the relation instead of the dimension name which enables you to choose which relation is used when there are multiple relations between dimensions. When no related-dimensions are specified, TALLY returns the total number of values in the current status of dimension.

Usage Notes

TALLY with NA

TALLY returns NA for any related-dimension position that has no dimension values corresponding to it.

TALLY with DWMQY Dimensions

When expression is dimensioned by a dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR, you can specify any other dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR as a related-dimension. Oracle OLAP uses the implicit relation between the dimensions. To control the mapping of one DAY, WEEK, MONTH, QUARTER, or YEAR dimension to another (for example, from weeks to months), you can define an explicit relation between the two dimensions and specify the name of the relation as the related-dimension argument to the TALLY function.

For each time period in the related dimension, Oracle OLAP tallies all the source time periods that end in the target time period. This method is used regardless of which dimension has the more aggregate time periods.

Examples

Example 8-130 Breaking Out TALLY Results

Here you use TALLY to determine how many products are produced by each division. The division.product relation records the division to which each product belongs. The following is a report of division.product.

PRODUCT          DIVISION.PRODUCT
---------------------------------
Tents          Camping
Canoes         Camping
Racquets       Sporting
Sportswear     Clothing
Footwear       Clothing

The following statement includes TALLY to present the number of products produced by each division.

REPORT HEADING 'Products' TALLY(product, division)

The statement produces this report.

DIVISION        Products
-------------------------
Camping                 2
Sporting                1
Clothing                2