PROB
Updated: 28 February 2011
Note: This documentation is for the SQL2008 (and later) version of this XLeratorDB function, it is not compatible with SQL Server 2005.
Click here for the SQL2005 version of the PROB function
Use PROB to calculate the probability that values in a range are between two limits. Syntax
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 Types
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 probabity 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
PROB
----------------------
0.2
(1 row(s) affected)
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
PROB
----------------------
0.52
(1 row(s) affected)
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.
grp PROB
----------- ----------------------
1 0.10429
2 0.11735
3 0.06959
4 0.03082
5 0.03679
(5 row(s) affected)