MULTINOMIAL_q
Updated: 17 August 2010
Use MULTINOMIAL_q to calculate the ratio of a sum of values to the product of the factorials of those values.
Syntax
SELECT [westclintech].[wct].[MULTINOMIAL_q] (
<@Values_RangeQuery, nvarchar(4000),>)
Arguments
@Values_RangeQuery
the SELECT statement to use in extracting data for the calculation. @Values_RangeQuery 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_q 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 m1.word
,wct.multinomial_q('SELECT m1.num_test from m1') as PERMUTATIONS
from m1
GROUP by m1.WORD
word PERMUTATIONS
-------------------------------------------------- ----------------------
MISSISSIPPI 34650
(1 row(s) affected)