The Partition() Function

The inspiration for this Tip was an initial reply by one of our resident Experts to a specific Post. In this Post, the User wanted to know how he could calculate the total number of test results (Satisfactory/Unsatisfactory) for specific age group intervals such as: 20-24, 25-29, etc. The use of the Partition Function was suggested, I followed up with a reply of my own, and it seemed as though some interest was generated in this little know Function. I decided to make this into a Tip, because even though it is a little known and rarely used Function, it is actually very handy when you want to calculate how many times something occurs within a calculated series of ranges or if you want to know if a number occurs within a calculated series of ranges. The syntax for the Partition Function is:

1. Partition(number, start, stop, interval)
   1. number - whole number to be evaluated against the ranges.
   2. start - whole number that is the start of the overall range of number.
   3. stop - whole number that is the end of the overall range of numbers.
   4. interval - difference between range elements.
2. The best method of explaining this Function is to show some test data, then apply the Function against the data with specific Arguments. The sample data, SQL Statement, and subsequent output are listed below:
   
   Expand|Select|Wrap|Line Numbers

   1. [Age]      [Testrslt] (Fields in tblTest)
   2.  
   3.  73        Satisfactory
   4.  19        Satisfactory
   5.  19        Unsatisfactory
   6.  20        Satisfactory
   7.  24        Unsatisfactory
   8.  21        Satisfactory
   9.  16        Satisfactory
   10.  45        Unsatisfactory
   11.  50        Satisfactory
   12.  55        Unsatisfactory
   13.  75        Satisfactory
   14.  80        Unsatisfactory
   15.  62        Satisfactory
   16.  64        Unsatisfactory
   17.  46        Unsatisfactory
   18.  47        Unsatisfactory
   19.  49        Unsatisfactory
   20.  48        Unsatisfactory
   21.  56        Unsatisfactory
   22.  58        Satisfactory
   23.  72        Satisfactory
   24.  67        Unsatisfactory
   25.  15        Satisfactory
   26.  17        Satisfactory
   27.  63        Unsatisfactory
   28.  66        Unsatisfactory
   29.  61        Unsatisfactory
   30.  33        Satisfactory
   31.  50        Satisfactory
   32.  51        Satisfactory
   33.  54        Satisfactory
   34.  32        Unsatisfactory
   Expand|Select|Wrap|Line Numbers

   1. SELECT DISTINCTROW [Testrslt], Partition([Age],0,100,5) AS Age_Range, Count([Age]) AS Range_Count
   2. FROM tblTest
   3. GROUP BY [Testrslt], Partition([Age],0,100,5);
   Expand|Select|Wrap|Line Numbers

   1. [Testrslt]       [Age_Range]  [Range_Count]
   2. Satisfactory     15: 19             4
   3. Satisfactory     20: 24             2
   4. Satisfactory     30: 34             1
   5. Satisfactory     50: 54             4
   6. Satisfactory     55: 59             1
   7. Satisfactory     60: 64             1
   8. Satisfactory     70: 74             2
   9. Satisfactory     75: 79             1
   10. Unsatisfactory     15: 19           1
   11. Unsatisfactory     20: 24           1
   12. Unsatisfactory     30: 34           1
   13. Unsatisfactory     45: 49           5
   14. Unsatisfactory     55: 59           2
   15. Unsatisfactory     60: 64           3
   16. Unsatisfactory     65: 69           2
   17. Unsatisfactory     80: 84           1