SQL Server PROB Function

Updated 2023-11-01 22:48:16.317000

Description

Use the aggregate function PROB to calculate the probability that values in a range are between two limits.

Syntax

SELECT [westclintech].[wct].[PROB] (
  <@x, float,> 
 ,<@p, float,> 
 ,<@lower_limit, float,> 
 ,<@upper_limit, float,>)

Arguments

@x

The number values, x, which are associated with the probabilities. @x is an expression of type float or of a type that can be implicitly converted to float.

@p

the set of probabilities associated with the @x values . @p is an expression of type float or of a type that can be implicitly converted to float.

@lower_limit

is the lower bound on the value for which you want a probability. @lower_limit is an expression of type float or of a type that can be implicitly converted to float.

@upper_limit

is the upper bound on the value for which you want a probability. @upper_limit is an expression of type float or of a type that can be implicitly converted to float.

Return Type

float

Remarks

If @p < 0 or @p > 1, then PROB returns an error.

If the sum of the values in @p > 1, PROB returns an error.

@upper_limit must be greater than or equal to @lower_limit.

@lower_limit must remain invariant for a group.

@upper_limit must remain invariant for a group.

PROB is an aggregate function and follows the same conventions as all other aggregate functions in SQL Server.

Examples

In this example, we will return the probability of x being equal to zero.

SELECT wct.PROB(x, p, 0, 0) as PROB
  FROM (   VALUES (0, 0.1),
                  (1, 0.01),
                  (2, 0.15),
                  (3, 0.25),
                  (5, 0.05),
                  (5, 0.06),
                  (6, 0.07),
                  (7, 0.12),
                  (8, 0.09),
                  (0, 0.1)) n (x, p);

This produces the following result

To return the probability of x being between 1 and 5:

SELECT wct.PROB(x, p, 1, 5) as PROB
  FROM (   VALUES (0, 0.1),
                  (1, 0.01),
                  (2, 0.15),
                  (3, 0.25),
                  (5, 0.05),
                  (5, 0.06),
                  (6, 0.07),
                  (7, 0.12),
                  (8, 0.09),
                  (0, 0.1)) n (x, p);

This produces the following result

In this example, we will calculate the probability of x being between 25 and 35 in each of 5 groups.

SELECT grp,
       wct.PROB(x, p, 25, 35) as PROB
  FROM (   VALUES (1, 62, 0.08102),
                  (1, 87, 0.05267),
                  (1, 9, 0.15542),
                  (1, 72, 0.11651),
                  (1, 61, 0.06826),
                  (1, 33, 0.10429),
                  (1, 18, 0.0792),
                  (1, 74, 0.02147),
                  (1, 98, 0.32116),
                  (2, 62, 0.15112),
                  (2, 87, 0.07222),
                  (2, 9, 0.07093),
                  (2, 72, 0.11023),
                  (2, 61, 0.01596),
                  (2, 33, 0.11735),
                  (2, 18, 0.14298),
                  (2, 74, 0.14736),
                  (2, 98, 0.17185),
                  (3, 62, 0.07902),
                  (3, 87, 0.05722),
                  (3, 9, 0.03252),
                  (3, 72, 0.08812),
                  (3, 61, 0.15774),
                  (3, 33, 0.06959),
                  (3, 18, 0.11124),
                  (3, 74, 0.08713),
                  (3, 98, 0.31742),
                  (4, 62, 0.10355),
                  (4, 87, 0.11084),
                  (4, 9, 0.09801),
                  (4, 72, 0.00072),
                  (4, 61, 0.13787),
                  (4, 33, 0.03082),
                  (4, 18, 0.04598),
                  (4, 74, 0.10491),
                  (4, 98, 0.3673),
                  (5, 62, 0.05577),
                  (5, 87, 0.08499),
                  (5, 9, 0.15781),
                  (5, 72, 0.12282),
                  (5, 61, 0.00356),
                  (5, 33, 0.03679),
                  (5, 18, 0.12075),
                  (5, 74, 0.0454),
                  (5, 98, 0.37211)) n (grp, x, p)
 GROUP BY grp;

This produces the following result.