Updated: 06 August 2010
Use MODE_q to return the MODE of the given numbers. The MODE is the number in the middle of a set of numbers.
Syntax
SELECT [wctStatistics].[wct].[MODE_q] (
<@Values_RangeQuery, nvarchar(4000),>)
Arguments
@Values_RangeQuery
the select statement, as text, used to determine the values to be used in the MODE_q calculation.
Return Types
float
Remarks
· MODE_q only includes values of type float or of a type that can be implicitly converted to float. All other values and NULL are excluded.
· If there are no values occurring more than once, MODE_q returns an error.
· For simpler queries consider using the MODE function.
· @Behavior is truncated to zero decimal places.
· If TRUNC(@Behavior,0) < 0 or TRUNC(@Behavior,0) > 2, MODE_q returns an an error.
· No GROUP BY is required for this function even though it produces aggregated results.
Examples
CREATE TABLE #m1(
[num] [float] NOT NULL
)
INSERT INTO #m1 VALUES (5.6)
INSERT INTO #m1 VALUES (4)
INSERT INTO #m1 VALUES (4)
INSERT INTO #m1 VALUES (3)
INSERT INTO #m1 VALUES (2)
INSERT INTO #m1 VALUES (3)
select wct.MODE_q('select num from #m1',0)
This produces the following result
----------------------
4
(1 row(s) affected)
CREATE TABLE #m2(
[num] [float] NOT NULL
)
INSERT INTO #m2 VALUES (1)
INSERT INTO #m2 VALUES (2)
INSERT INTO #m2 VALUES (3)
INSERT INTO #m2 VALUES (1)
INSERT INTO #m2 VALUES (2)
INSERT INTO #m2 VALUES (3)
select wct.MODE_q('select num from #m2',0)
This produces the following result
----------------------
1
(1 row(s) affected)