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

Part Number E17122-05
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
View PDF

MODE

The MODE function returns the mode (the most frequently occurring value) of a numeric expression. When there are no duplicate values in the data, then MODE returns NA.

Return Value

DECIMAL

Syntax

MODE(expression [CACHE} [dimensions])

Parameters

expression

The numeric expression whose mode is to be calculated.

CACHE

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

dimensions

The dimensions of the result. When you do not specify any dimensions, MODE calculates the mode over all the dimensions of expression and it returns a single value. When you specify one or more dimensions (but fewer than all of the dimensions of expression) in the dimension argument, then MODE calculates the mode for each value of the dimensions that you specified and returns an array of values. Each dimension must be a dimension of expression.

Usage Notes

The Effect of NASKIP on MODE

MODE is not affected by the NASKIP option.

More Than One Set of Duplicate Values

When multiple values qualify as having the greatest number of occurrences in the expression, then MODE sorts the values and returns the lowest one. For example, for the data series {4,5,2,3,7,4,6,2,1}, the mode for the series is 2 even though 2 and 4 both occur twice.

Examples

Example 8-27 Reporting the Mode

These examples use the following geography and items dimensions and sales2 variable.

DEFINE geography DIMENSION TEXT 
MAINTAIN geography ADD 'g1' 'g2' 'g3'
DEFINE items DIMENSION TEXT
MAINTAIN items ADD 'Item1' 'Item2' 'Item3' 'Item4' 'Item5'
DEFINE sales2 DECIMAL <geography items>

Assume the sales2 variable has the following data values.

-------------SALES2-------------
               -----------GEOGRAPHY------------
ITEMS              G1         G2         G3
-------------- ---------- ---------- ----------
Item1               30.00      15.00      12.00
Item2               10.00      20.00      18.00
Item3               15.00      20.00      24.00
Item4               30.00      25.00      25.00
Item5                  NA       7.00      21.00
  • This statement reports the mode that is calculated over the geography dimension.

    REPORT W 22 MODE(sales2, geography)
    

    The preceding statement produces the following output.

    MODE(SALES2,
    GEOGRAPHY            GEOGRAPHY)
    -------------- ----------------------
    g1                              30.00
    g2                              20.00
    g3                                 NA
    
  • This statement reports the mode that is calculated over the items dimension.

    REPORT W 18 MODE(sales2, items)
    

    The preceding statement produces the following output.

    MODE(SALES2,
    ITEMS                ITEMS)
    -------------- ------------------
    Item1                          NA
    Item2                          NA
    Item3                          NA
    Item4                       25.00
    ITEM5                          NA
    
  • This statement reports the mode that is calculated over all of the dimensions of the sales2 variable.

    REPORT MODE(sales2)
    

    The preceding statement produces the following output.

    Mode
    ----
    15