Login     Register

        Contact Us     Search

XLeratorDB/math Documentation

SQL Server MULITNOMIAL function


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)



Copyright 2008-2017 Westclintech LLC         Privacy Policy        Terms of Service