 # SQL Server NEGBINOM.DIST function

NEGBINOM_DIST

Updated: 31 July 2015

Use NEGBINOM_DIST to calculate the probability mass function or the cumulative distributive function of the negative binomial distribution. The negative binomial distribution calculates the probability of k successes occurring before r failures in a sequence of Bernoulli trials.
Syntax
SELECT [wct].[NEGBINOM_DIST](
<@Number_f, int,>
,<@Number_s, int,>
,<@Probability_s, float,>
,<@Cumulative, bit,>)
Arguments
@Number_f
the number of failures. @Number_f must be of a type int or of type that intrinsically converts to int.
@Number_s
the number of successes. @Number_s must be of a type int or of type that intrinsically converts to int.
@Probability_s
the probability of success. @Probability_s must be of a type float or of type that intrinsically converts to float.
@Cumulative
a bit value identifying whether the probability mass function ('False') or the cumulative distribution function ('True') is to be returned. @Cumulative is of a type bit or a type that implicitly converts to bit.
Return Type
float
Remarks
·         0 = @Number_f
·         0 < @Number_s
·         0 = @P = 1
Examples
Stephen Curry of the Golden State Warriors makes 91.4% of his free throws. In a game in which he attempts 7 free throws what is the likelihood the he will miss 2?
SELECT
wct.NEGBINOM_DIST(
2            --@Number_f
,5            --@Number_s
,.914         --@Probability_s
,'False'      --@Cumulative
) as NEGBINOM_DIST

This produces the following result

This is the probability that he will miss exactly 2 free throws. To calculate the possibility that we will miss no more than 2 three throws we would enter the following SQL.
SELECT
wct.NEGBINOM_DIST(
2            --@Number_f
,5            --@Number_s
,.914         --@Probability_s
,'True'       --@Cumulative
) as NEGBINOM_DIST

This produces the following result.

The calculation of the probability mass function is closely related to the combinatorial function.
SELECT
wct.NEGBINOM_DIST(failures,trials-failures,p,'False') as NEGBINOM_DIST
,wct.COMBIN(trials-1,failures)*POWER(p,trials-failures)*POWER(1-p,failures) as COMBIN
FROM (VALUES (2,7,cast(0.914 as float)))n(failures,trials,p)

This produces the following result.

The cumulative distribution function is closely related to the Beta distribution.
SELECT
wct.NEGBINOM_DIST(failures,trials-failures,p,'True') as NEGBINOM_DIST
,wct.BETA_DIST(p,trials-failures,failures+1,'True',NULL,NULL) as BETA_DIST
FROM (VALUES (2,7,cast(0.914 as float)))n(failures,trials,p)

This produces the following result.

### Support  Copyright 2008-2023 Westclintech LLC         Privacy Policy        Terms of Service