MULTINOMIAL_q

Updated 2023-10-13 20:36:38.043000

Syntax

SELECT [westclintech].[wct].[MULTINOMIAL_q] (
   <@Values_RangeQuery, nvarchar(4000),>)

Description

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

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 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_q 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 m1.word,

       wct.multinomial_q('SELECT m1.num_test from m1') as PERMUTATIONS

from m1

GROUP by m1.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"}]}