Categories:

Aggregate Functions (General) , Window Functions (Window Frame)

MODE

Returns the most frequent value for the values within expr1. NULL values are ignored. If all the values are NULL, or there are 0 rows, then the function returns NULL.

Syntax

Aggregate function

MODE( <expr1> )

Window function

MODE( <expr1> ) OVER ( [ PARTITION BY <expr2> ] )

Arguments

expr1

This expression produces the values that are searched to find the most frequent value. The expression can be of any data type.

expr2

The optional expression on which to partition the data into groups. The output contains the most frequent value for each group/partition.

Returns

The data type of the returned value is identical to the data type of the input expression.

Usage Notes

  • If there is a tie for most frequent value (two or more values occur as frequently as each other, and more frequently than any other value), MODE returns one of those values.

  • DISTINCT is not supported for this function.

  • Even if NULL is the most frequent value, the function does not return NULL (unless all values are NULL).

  • When used as a window functions:

    • This function does not support:

      • ORDER BY sub-clause in the OVER() clause. The order of the values does not matter when choosing the most frequent value.

      • Window frames.

Examples

The following code demonstrates the use of MODE:

Create a table and data:

create or replace table aggr(k int, v decimal(10,2));

Get the MODE value for column v. The function returns NULL because there are no rows.

select mode(v) from aggr;
+---------+
| MODE(V) |
|---------|
|    NULL |
+---------+

Insert some rows:

INSERT INTO aggr (k, v) VALUES
    (1, 10), 
    (1, 10), 
    (1, 20), 
    (1, 21);

MODE returns the most frequent value 10:

select mode(v) from aggr;
+---------+
| MODE(V) |
|---------|
|   10.00 |
+---------+

Insert some more rows:

INSERT INTO aggr (k, v) VALUES
    (2, 20), 
    (2, 25), 
    (2, 30);

Now there are two most frequent values. MODE selects the value 10:

select mode(v) from aggr;
+---------+
| MODE(V) |
|---------|
|   10.00 |
+---------+

Insert a row with NULL value:

INSERT INTO aggr (k, v) VALUES (3, null);

Get the MODE value for each group. Note that because the values in group k = 3 only contains NULL values, the returned value for that group is NULL.

select k, mode(v) 
    from aggr 
    group by k
    order by k;
+---+---------+
| K | MODE(V) |
|---+---------|
| 1 |   10.00 |
| 2 |   20.00 |
| 3 |    NULL |
+---+---------+

MODE can also be used as a basic window function with an OVER() clause:

select k, v, mode(v) over (partition by k) 
    from aggr 
    order by k, v;
+---+-------+-------------------------------+
| K |     V | MODE(V) OVER (PARTITION BY K) |
|---+-------+-------------------------------|
| 1 | 10.00 |                         10.00 |
| 1 | 10.00 |                         10.00 |
| 1 | 20.00 |                         10.00 |
| 1 | 21.00 |                         10.00 |
| 2 | 20.00 |                         20.00 |
| 2 | 25.00 |                         20.00 |
| 2 | 30.00 |                         20.00 |
| 3 |  NULL |                          NULL |
+---+-------+-------------------------------+