MULTINOMIAL
Updated: 17 August 2010
Use MULTINOMIAL to calculate the ratio of a sum of values to the product of the factorials of those values.
Syntax
SELECT [westclintech].[wct].[MULTINOMIAL] (
<@Values_TableName, nvarchar(4000),>
,<@Values_ColumnName, nvarchar(4000),>
,<@Values_GroupedColumnName, nvarchar(4000),>
,<@Values_GroupedColumnValue, sql_variant,>)
Arguments
@Values_TableName
the name of the table which contains the coefficients. @Values _TableName is an expression of character data. @Values _TableName must be of a data type that is implicitly convertible to varchar.
@Values _ColumnName
the name of the column in the table which contains the coefficients. @Values _ColumnName is an expression of character data. @Values _ColumnName must be of a data type that is implicitly convertible to varchar.
@Values _GroupedColumnName
the name of the column in the table which in which the results will be grouped. @Values _GroupedColumnName is an expression of character data. @Values _GroupedColumnName must be of a data type that is implicitly convertible to varchar.
@Values _GroupedColumnValue
the value in the Grouped Column that is to be used for aggregating the results. @Values _GroupedColumnValue is an expression of character data. @Values _GroupedColumnValue must be of a data type that is implicitly convertible to varchar.
Return Types
float
Remarks
· If any value is zero than the result of the calculation will be zero
· If any value is less than zero, then MULTINOMIAL will return an error
Example
Here’s an example where we calculate the number of permutations of the letters contained in ‘MISSISSIPPI’
Create the following table, which is holding each letter and the number of occurrences
CREATE TABLE [dbo].[m1](
[word] [varchar](50) NOT NULL,
[letter] [char](1) NOT NULL,
[num_test] [float] NOT NULL,
CONSTRAINT [PK_m1] PRIMARY KEY CLUSTERED
(
[word] ASC,
[letter] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Insert data into the table that was just created.
INSERT INTO m1 select 'MISSISSIPPI','M', Len('MISSISSIPPI') - len(replace('MISSISSIPPI', 'M',''))
INSERT INTO m1 select 'MISSISSIPPI','I', Len('MISSISSIPPI') - len(replace('MISSISSIPPI', 'I',''))
INSERT INTO m1 select 'MISSISSIPPI','S', Len('MISSISSIPPI') - len(replace('MISSISSIPPI', 'S',''))
INSERT INTO m1 select 'MISSISSIPPI','P', Len('MISSISSIPPI') - len(replace('MISSISSIPPI', 'P',''))
Run the following SELECT statement.
select word
,wct.multinomial('m1','num_test','word', word) as PERMUTATIONS
from m1
group by word
word PERMUTATIONS
-------------------------------------------------- ----------------------
MISSISSIPPI 34650
(1 row(s) affected)