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