| Oracle® OLAP DML Reference 10g Release 2 (10.2) Part Number B14346-01 |
|
|
View PDF |
| Oracle® OLAP DML Reference 10g Release 2 (10.2) Part Number B14346-01 |
|
|
View PDF |
The AGGCOUNT function retrieves the values of the Aggcount variable associated with the specified variable. An Aggcount variable is an INTEGER variable that Oracle OLAP automatically creates when it creates a variable defined using a DEFINE VARIABLE statement that includes a USING AGGOUNT phrase.
Return Value
INTEGER
The values of the Aggcount variable that are the non-NA counts of the number of leaf nodes that contribute to the calculation of aggregate values when RELATION (for aggregation) statements that have an AVERAGE, HAVERAGE, WAVERAGE, or HWAVERAGE execute.
Syntax
AGGCOUNT(variable-name)
Arguments
The name of the variable with which the Aggcount variable is associated.
Examples
Example 6-20 Reporting on an Aggcount Variable
Assume that within your analytic workspace you have objects with the following definitions.
DEFINE geog DIMENSION TEXT DEFINE time DIMENSION TEXT DEFINE cc_geog_product COMPOSITE <geog product> COMPRESSED DEFINE sales VARIABLE DECIMAL <time cc_geog_product <geog product>> WITH AGGCOUNT DEFINE geog_parentrel RELATION geog <geog> DEFINE product_parentrel RELATION product <product> DEFINE time_parentrel RELATION time <time> DEFINE aggsales AGGMAP AGGMAP RELATION time_parentrel OPERATOR AVERAGE RELATION geog_parentrel RELATION product_parentrel END
Notice that the definition for the sales variable includes a request for an Aggcount variable and that, within the aggsales aggmap, the RELATION statement for the time_parentrel relation incudes an AVERAGE operator.
Assume also that when only the base values of the sales variable are populated, sales has the following values for Radios and TVs.
REPORT sales
PRODUCT: Radio
-------------SALES--------------
--------------TIME--------------
GEOG 2004 2005 Average
-------------- ---------- ---------- ----------
Maine 122.93 176.69 NA
California 168.32 150.92 NA
Quebec NA NA NA
Ontario 187.46 164.46 NA
USA NA NA NA
Canada NA NA NA
World NA NA NA
PRODUCT: TV
-------------SALES--------------
--------------TIME--------------
GEOG 2004 2005 Average
-------------- ---------- ---------- ----------
Maine 184.75 135.40 NA
California 139.89 145.71 NA
Quebec NA NA NA
Ontario 123.63 113.32 NA
USA NA NA NA
Canada NA NA NA
World NA NA NA
PRODUCT: AV
-------------SALES--------------
--------------TIME--------------
GEOG 2004 2005 Average
-------------- ---------- ---------- ----------
Maine NA NA NA
California NA NA NA
Quebec NA NA NA
Ontario NA NA NA
USA NA NA NA
Canada NA NA NA
World NA NA NA
Since no aggregation has occurred, for AV, Oracle OLAP has not yet populated the Aggcount variable and the Aggcount variable for sales contains only NA values.
Now assume that you aggregate the sales variable by issuing the following statement.
AGGREGATE sales USING aggsales
Once aggregated a report of sales shows the following values.
REPORT sales
PRODUCT: Radio
-------------SALES--------------
--------------TIME--------------
GEOG 2004 2005 Average
-------------- ---------- ---------- ----------
Maine 122.93 176.69 149.81
California 168.32 150.92 159.62
Quebec NA NA NA
Ontario 187.46 164.46 175.96
USA 291.24 327.61 309.42
Canada 187.46 164.46 175.96
World 478.70 492.07 485.38
PRODUCT: TV
-------------SALES--------------
--------------TIME--------------
GEOG 2004 2005 Average
-------------- ---------- ---------- ----------
Maine 184.75 135.40 160.07
California 139.89 145.71 142.80
Quebec NA NA NA
Ontario 123.63 113.32 118.47
USA 324.64 281.11 302.87
Canada 123.63 113.32 118.47
World 448.27 394.42 421.35
PRODUCT: AV
-------------SALES--------------
--------------TIME--------------
GEOG 2004 2005 Average
-------------- ---------- ---------- ----------
Maine 307.67 312.08 309.88
California 308.21 296.63 302.42
Quebec NA NA NA
Ontario 311.09 277.78 294.43
USA 615.88 608.71 612.30
Canada 311.09 277.78 294.43
World 926.97 886.49 906.73
A report of the Aggcount variable shows that it is populated with the INTEGER values that are needed to aggregate the average sales.
REPORT AGGCOUNT (sales)
PRODUCT: Radio
--------AGGCOUNT (SALES)--------
--------------TIME--------------
GEOG 2004 2005 Average
-------------- ---------- ---------- ----------
Maine NA NA 2
California NA NA 2
Quebec NA NA NA
Ontario NA NA 2
USA 2 2 4
Canada NA NA 2
World 3 3 6
PRODUCT: TV
--------AGGCOUNT (SALES)--------
--------------TIME--------------
GEOG 2004 2005 Average
-------------- ---------- ---------- ----------
Maine NA NA 2
California NA NA 2
Quebec NA NA NA
Ontario NA NA 2
USA 2 2 4
Canada NA NA 2
World 3 3 6
PRODUCT: AV
--------AGGCOUNT (SALES)--------
--------------TIME--------------
GEOG 2004 2005 Average
-------------- ---------- ---------- ----------
Maine 2 2 4
California 2 2 4
Quebec NA NA NA
Ontario 2 2 4
USA 4 4 8
Canada 2 2 4
World 6 6 12
