Logo

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

@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.

@behavior

identifies how to handle ties.

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"}]}