The TALLY function counts the number of values of a dimension that correspond to each value of one or more related dimensions.
INTEGER
TALLY(dimension [related-dimensions])
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.
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. This enables you to choose which relation is used when there is more than one. When no related-dimensions are specified, TALLY returns the total number of values in the current status of dimension.
TALLY returns NA
for any related-dimension position that has no dimension values corresponding to it.
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.
Example 8-128 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