MODE

Updated 2023-10-23 20:35:36.177000

Syntax

SELECT [westclintech].[wct].[MODE] (
  <@known_x, float,>
 ,<@behavior, int,>)

Description

Use the aggregate function MODE to return the mode of the given numbers. The mode of a set of numbers is the one that occurs most often.

Arguments

@behavior

identifies how to handle ties.

@known_x

the known x-values to be used in the MODE calculation. @Known_x is an expression of type float or of a type that can be implicitly converted to float.

Return Type

float

Remarks

If there are no values occurring more than once, MODE returns NULL.

@Behavior is truncated to zero decimal places.

If @Behavior  < 0 or @Behavior  > 2, MODE returns an error.

Set @Behavior = 0 to return the lowest MODE value when there are ties

Set @Behavior = 1 to return the greatest MODE value when there are ties

Set @Behavior = 2 to return NULL when there are ties.

@Behavior must be invariant within a GROUP.

MODE is an aggregate function and follows the same conventions as all other AGGREGATE functions in SQL Server.

Examples

In this example, each value is unique, so MODE will return a NULL.

SELECT wct.MODE(x, 0) as MODE
FROM
(
    VALUES
        (1),
        (2),
        (3),
        (4),
        (5),
        (6),
        (7),
        (8),
        (9),
        (10)
) m (x);

This produces the following result.

{"columns":[{"field":"MODE"}],"rows":[{"MODE":"NULL"}]}

In this example, 2 occurs twice and 7 occurs 3 times.

SELECT wct.MODE(x, 0) as MODE
FROM
(
    VALUES
        (1),
        (2),
        (2),
        (4),
        (5),
        (7),
        (7),
        (7),
        (9),
        (10)
) m (x);

This produces the following result.

{"columns":[{"field":"MODE"}],"rows":[{"MODE":"7"}]}

In this exemple, 2 and 7 both occur twice.

SELECT wct.MODE(x, 0) as [MODE 0],
       wct.MODE(x, 1) as [MODE 1],
       wct.MODE(x, 2) as [MODE 2]
FROM
(
    VALUES
        (1),
        (2),
        (2),
        (4),
        (5),
        (7),
        (7),
        (8),
        (9),
        (10)
) n (x);

This produces the following result.

{"columns":[{"field":"MODE 0","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"MODE 1","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"MODE 2"}],"rows":[{"MODE 0":"2","MODE 1":"7","MODE 2":"NULL"}]}