MULTINOMIAL

Updated 2023-10-13 20:33:39.917000

Syntax

SELECT [westclintech].[wct].[MULTINOMIAL] (
  <@Values_TableName, nvarchar(4000),>
 ,<@Values_ColumnName, nvarchar(4000),>
 ,<@Values_GroupedColumnName, nvarchar(4000),>
 ,<@Values_GroupedColumnValue, sql_variant,>)

Description

Use the scalar function MULTINOMIAL to calculate the ratio of a sum of values to the product of the factorials of those values.

Arguments

@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_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_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_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 Type

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.

Examples

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;

This produces the following result.

{"columns":[{"field":"word"},{"field":"PERMUTATIONS","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"word":"MISSISSIPPI","PERMUTATIONS":"34650"}]}