SQL Server BINOM_DIST_RANGE Function

Updated 2024-02-13 20:44:08.533000

Description

Use the scalar function BINOM_DIST_RANGE to calculate the probability of a trial result using a binomial distribution.

Syntax

SELECT [westclintech].[wct].[BINOM_DIST_RANGE](
  <@Trials, int,>
 ,<@Probability_s, float,>
 ,<@Number_s, int,>
 ,<@Number_s2, int,>)

Arguments

@Trials

The number of independent trials. @Trials must be of a type int or of type that intrinsically converts to int.

@Probability_s

The probability of success in each trial. @Probability_s must be of a type float or of a type that intrinsically converts to float.

@Number_s

The number of successes in @Trials. @Number_s must be of a type int or of a type that intrinsically converts to int.

@Number_s2

The upper bound of the number of successes in @Trials. @Number_s2 must be of a type int or of a type that intrinsically converts to int.

Return Type

float

Remarks

@Trials > 0.

0 ≤ @Probability_s ≤ 1.

0 ≤ @Number_s = @Trials

If NOT NULL then 0 ≤ @Number_s2 ≤ @Trials

Examples

Calculate the probability of having exactly 48 successes in 60 trials when the probability of success is 75%.

SELECT wct.BINOM_DIST_RANGE(   60,   --@Trials
                               0.75, --@Probability_s
                               48,   --@Number_s
                               NULL  --@Number_s2
                           ) as BINOM_DIST_RANGE;

This produces the following result.

This is actually no different than calculating the probability mass function of the binomial distribution.

SELECT wct.BINOMDIST(48, 60, 0.75, 'False') as PMF;

This produces the following result.

Let's look at another example where we calculate the probability of having exactly 500 successes in 1,000 trial where the probability of success is 50%.

SELECT wct.BINOM_DIST_RANGE(   1000, --@Trials
                               0.50, --@Probability_s
                               500,  --@Number_s
                               NULL  --@Number_s2
                           ) as BINOM_DIST_RANGE;

This produces the following result.

It might be far more useful, however, to calculate the probability of having between 450 and 550 successes.

SELECT wct.BINOM_DIST_RANGE(   1000, --@Trials
                               0.50, --@Probability_s
                               450,  --@Number_s
                               550   --@Number_s2
                           ) as BINOM_DIST_RANGE;

This produces the following result.

In this example we bucket the results into 20 equal groups using the XLeratorDB SeriesInt function.

SELECT seriesvalue - 49 as lb,
       seriesvalue as ub,
       wct.BINOM_DIST_RANGE(1000, 0.50, seriesvalue - 49, seriesvalue) as p
FROM wct.SeriesInt(50, 1000, 50, NULL, NULL);

This produces the following result.

See Also

BETA_DIST - Calculate pdf or cdf of beta distribution

BINOMDIST - Binomial distribution

BINOMINV - Inverse (quantile) of the binomial distribution

FACTLN - natural logarithm of a factorial