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 |
+---+-------+-------------------------------+
``` 