Skip Headers
Oracle® OLAP DML Reference
11g Release 2 (11.2)

E17122-08
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

PERCENTAGE

The PERCENTAGE function computes the percent of total for each value in a numeric expression.

Return Value

DECIMAL

Syntax

PERCENTAGE(expression [CACHE] [BASEDON dimension-list])

Parameters

expression

The numeric expression for which percent figures are to be computed.

CACHE

Specifies slightly different internal behavior. Specify this keyword only when the original performance is extremely slow.

BASEDON dimension-list

An optional list of one or more of the dimensions of expression on which to base the percentage for each value. When you do not specify the dimensions, then PERCENTAGE bases the percentage on the total of all of the values of all of the dimensions of expression.

Usage Notes

The Effect of NASKIP on PERCENTAGE

PERCENTAGE is affected by the NASKIP option. When NASKIP is set to YES (the default), then PERCENTAGE ignores NA values. When NASKIP is set to NO, then PERCENTAGE returns NA for any cell in expression whose value is NA.

Examples

Example 8-61 Calculating the Percentage

The following statements s limit the month and district dimensions, and report the data values, with subtotals, for the units variable.

LIMIT month TO 'Jul96' TO 'Sep96'
LIMIT district TO 'Denver'
REPORT SUBTOTALS W 8 units

The preceding statement produces the following output.

DISTRICT: DENVER
               ----------UNITS-----------
               ----------MONTH-----------
PRODUCT         Jul96    Aug96    Sep96
-------------- -------- -------- --------
Tents               608      517      441
Canoes              467      363      411
Racquets          3,006    2,836    2,838
Sportswear        2,395    2,039    2,138
Footwear          1,581    1,532    1,667
-------------- -------- -------- --------
TOTAL DENVER      8,057    7,287    7,495

This statement reports the percentage that each month value represents of the total month values for each of the product values that are in status. The total of the values that PERCENTAGE returns for each product value is 1.

REPORT SUBTOTALS W 8 DOWN month PERCENTAGE(units BASEDON month)
DISTRICT: DENVER
         -----------PERCENTAGE(UNITS BASEDON MONTH)------------
         -----------------------PRODUCT------------------------
MONTH      Tents      Canoes    Racquets  Sportswear  Footwear
-------- ---------- ---------- ---------- ---------- ----------
Jul96          0.39       0.38       0.35       0.36       0.33
Aug96          0.33       0.29       0.33       0.31       0.32
Sep96          0.28       0.33       0.33       0.33       0.35
-------- ---------- ---------- ---------- ---------- ----------
TOTAL          1.00       1.00       1.00       1.00       1.00
DENVER

This statement reports the percentage that each product value represents of the total product values for each of the month values that are in status.

REPORT SUBTOTALS W 8 PERCENTAGE(units BASEDON product)
DISTRICT: DENVER
               -PERCENTAGE(UNITS BASEDON-
               ---------PRODUCT)---------
               ----------MONTH-----------
PRODUCT         Jul96    Aug96    Sep96
-------------- -------- -------- --------
Tents              0.08     0.07     0.06
Canoes             0.06     0.05     0.05
Racquets           0.37     0.39     0.38
Sportswear         0.30     0.28     0.29
Footwear           0.20     0.21     0.22
-------------- -------- -------- --------
TOTAL DENVER       1.00     1.00     1.00

This statement reports the percentage based on all of the dimensions of the units variable. The total of all of the values that PERCENTAGE returns is 1.

REPORT SUBTOTALS W 8 PERCENTAGE(units)
DISTRICT: DENVER
               ----PERCENTAGE(UNITS)-----
               ----------MONTH-----------
PRODUCT         Jul96    Aug96    Sep96
-------------- -------- -------- --------
Tents              0.03     0.02     0.02
Canoes             0.02     0.02     0.02
Racquets           0.13     0.12     0.12
Sportswear         0.10     0.09     0.09
Footwear           0.07     0.07     0.07
-------------- -------- -------- --------
TOTAL DENVER       0.35     0.32     0.33

The total for all of the values for both the product and month dimensions is 1.00.