Login     Register

        Contact Us     Search

XLeratorDB/statistics Documentation

SQL Server PROB function


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
XLeratorDB syntax for PROB function for SQL Server
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)



Copyright 2008-2017 Westclintech LLC         Privacy Policy        Terms of Service