MODE
Updated: 28 February 2011
Use the aggregate function MODE to return the most common non-NULL value in a dataset.
Syntax
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 Types
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.
· If you have previously used the MODE scalar function, the MODE aggregate has a different syntax. The MODE scalar function is no longer available in XLeratorDB/statistics2008.
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.
MODE
----------------------
NULL
(1 row(s) affected)
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.
MODE
----------------------
7
(1 row(s) affected)
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.
MODE 0 MODE 1 MODE 2
---------------------- ---------------------- ----------------------
2 7 NULL
(1 row(s) affected)