MODE
Updated: 28 February 2011
Note: This documentation is for the SQL2008 (and later) version of this XLeratorDB function, it is not compatible with SQL Server 2005.
Click here for the SQL2005 version of the MODE function
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.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.
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.
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.