The WIDTH_BUCKET function is used to create equal-width histograms.

The schema is SYSIBM.

expression

An expression that specifies the value to be assigned into a bucket.
The expression must return a value that is a built-in numeric, CHAR, or VARCHAR data type.
In a Unicode database, the expression can also be a GRAPHIC or VARGRAPHIC data type.
CHAR, VARCHAR, GRAPHIC, and VARGRAPHIC are supported by using implicit casting.
If the data type is DECFLOAT, the value must not be a special value such as NaN or INFINITY (SQLSTATE 42815).

bound1

An expression that specifies the left end point.
The expression must return a value that is a built-in numeric, CHAR, or VARCHAR data type.
In a Unicode database, the expression can also be a GRAPHIC or VARGRAPHIC data type.
CHAR, VARCHAR, GRAPHIC, and VARGRAPHIC are supported by using implicit casting.
If the data type is DECFLOAT, the value must not be a special value such as NaN or INFINITY (SQLSTATE 42815).

bound2

An expression that specifies the right end point.
The expression must return a value that is a built-in numeric, CHAR, or VARCHAR data type.
In a Unicode database, the expression can also be a GRAPHIC or VARGRAPHIC data type.
CHAR, VARCHAR, GRAPHIC, and VARGRAPHIC are supported by using implicit casting.
If the data type is DECFLOAT, the value must not be a special value such as NaN or INFINITY (SQLSTATE 42815).
bound1 must not be equal to bound2 (SQLSTATE 2201G).

num-buckets

An expression that specifies the number of buckets between bound1 and bound2.
The expression must return a value that is a SMALLINT, INTEGER, BIGINT, DECIMAL, DECFLOAT, CHAR, or VARCHAR data type.
In a Unicode database, the expression can also be a GRAPHIC or VARGRAPHIC data type.
CHAR, VARCHAR, GRAPHIC, and VARGRAPHIC are supported by using implicit casting.
If the expression is a CHAR, VARCHAR, a GRAPHIC, or VARGRAPHIC, it is cast to DECFLOAT(34) before the function is evaluated.
If the value is a DECIMAL or DECFLOAT, it is truncated to zero places to the left of the decimal point.
The value must be greater than 0 (SQLSTATE 2201G).
If the data type is DECFLOAT, the value must not be a special value such as NaN or INFINITY (SQLSTATE 42815).

The data type of the result is based on the data type of num-buckets.

Table 1. Data type of the result

This function returns the bucket number that expression falls into given bound1, bound2, and num-buckets.
The range from bound1 to bound2 is divided into num-buckets buckets starting from bucket 1 to bucket num-buckets.

If any argument can be null, the result can be null.
If any argument is null, the result is the null value.

Notes

• If bound1 is less than bound2, each bucket is a left-closed, right-open interval on the real line.
  If expression is less than bound1, the result is 0, which represents an underflow bucket.
  If expression is greater than or equal to bound2, the result is num-buckets + 1, which represents an overflow bucket.
• If bound1 is greater than bound2, each bucket is a left-closed, right-open interval on the real line.
  If expression is greater than bound1, the result is 0, which represents an underflow bucket.
  If expression is less than or equal to bound2, the result is num-buckets + 1, which represents an overflow bucket.
• When num-buckets is the maximum value for the data type, an error is returned if the result is num-buckets + 1 (SQLSTATE 22003).
• Several arithmetic operations are used to compute the result.
  If any of these arithmetic operations result in an overflow, an error is returned (SQLSTATE 22003).

Example

Using the EMPLOYEE table, assign a bucket to each employee's salary using a range of 35000 to 100000 divided into 13 buckets.

   SELECT EMPNO
   ,      SALARY
   ,      WIDTH_BUCKET(SALARY, 35000, 100000, 13)
   FROM   EMPLOYEE 
   ORDER  BY EMPNO

15 buckets are assigned with the following ranges:

• Bucket 0: salary < 35000
• Bucket 1: 35000 <= salary < 40000
• Bucket 2: 40000 <= salary < 45000
• Bucket 3: 45000 <= salary < 50000
• Bucket 4: 50000 <= salary < 55000
• Bucket 5: 55000 <= salary < 60000
• Bucket 6: 60000 <= salary < 65000
• Bucket 7: 65000 <= salary < 70000
• Bucket 8: 70000 <= salary < 75000
• Bucket 9: 75000 <= salary < 80000
• Bucket 10: 80000 <= salary < 85000
• Bucket 11: 85000 <= salary < 90000
• Bucket 12: 90000 <= salary < 95000
• Bucket 13: 95000 <= salary < 100000
• Bucket 14: salary >= 100000

The query has the following output:

   EMPNO  SALARY      3          
   ------ ----------- -----------
   000010   152750.00          14
   000020    94250.00          12
   000030    98250.00          13
   000050    80175.00          10
   000060    72250.00           8
   000070    96170.00          13
   000090    89750.00          11
   000100    86150.00          11
   000110    66500.00           7
   000120    49250.00           3
   000130    73800.00           8
   000140    68420.00           7
   000150    55280.00           5
   000160    62250.00           6
   000170    44680.00           2
   000180    51340.00           4
   000190    50450.00           4
   000200    57740.00           5
   000210    68270.00           7
   000220    49840.00           3
   000230    42180.00           2
   000240    48760.00           3
   000250    49180.00           3
   000260    47250.00           3
   000270    37380.00           1
   000280    36250.00           1
   000290    35340.00           1
   000300    37750.00           1
   000310    35900.00           1
   000320    39950.00           1
   000330    45370.00           3
   000340    43840.00           2
   200010    46500.00           3
   200120    39250.00           1
   200140    68420.00           7
   200170    64680.00           6
   200220    69840.00           7
   200240    37760.00           1
   200280    46250.00           3
   200310    35900.00           1
   200330    35370.00           1
   200340    31840.00           0

   42 record(s) selected.