473,888 Members | 2,213 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

The Partition() Function

8,834 Recognized Expert Expert
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(numbe r, 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)
    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
Sep 16 '07 #1
8 17348
3,080 Recognized Expert Specialist
Hi ADezii, thanks for teaching me a new function :)

Personally I prefer however to use the "table approach" as this will allow me to have the ranges specified by the user, thus placing the responsibility in his (her) hands. You know how often users can change their mind :-)

Jan 19 '10 #2
8,834 Recognized Expert Expert
Point well taken, Nico.
Jan 19 '10 #3
14,534 Recognized Expert Moderator MVP
Although I agree with Nico I appreciate learning a new function. Never know when it might come in useful. :D
Jan 19 '10 #4
15 New Member
Here's a frequency distribution approach that uses a table with the group definitions: Frequency Distributions
May 9 '10 #5
8,834 Recognized Expert Expert
Thanks for Posting this informational Link.
May 9 '10 #6
1,278 Recognized Expert Top Contributor
Does this work in Access? Isn't DistinctRow SQL-only?
Jun 12 '10 #7
8,834 Recognized Expert Expert
The Partition() Function will work in Access.
Jun 12 '10 #8
14,534 Recognized Expert Moderator MVP
Not sure if DISTINCTROW does anything Jim but it doesn't throw an error.
Jun 12 '10 #9

Sign in to post your reply or Sign up for a free account.

Similar topics

by: Xah Lee | last post by:
here's another interesting algorithmic exercise, again from part of a larger program in the previous series. Here's the original Perl documentation: =pod merge($pairings) takes a list of pairs, each pair indicates the sameness of the two indexes. Returns a partitioned list of same indexes.
by: Xah Lee | last post by:
another functional exercise with lists. Here's the perl documentation. I'll post a perl and the translated python version in 48 hours. =pod parti(aList, equalFunc) given a list aList of n elements, we want to return a list that is a
by: Mark Dengler | last post by:
I am trying to write a C# backend program that will handle the database maintenance that I would otherwise have to do via Microsoft's Analysis Services front end GUI Wizards. Currently I am having a problem with recreating the SliceValue. The value is a time dimension and should equate to ... and instead it is these same values, but instead of being seperated by periods it has some sort of non-displayable character (maybe a newline)...
by: sameer_deshpande | last post by:
Hi, I need to create a partition table but the column on which I need to create a partition may not have any logical ranges. So while creating or defining partition function I can not use any range. like CREATE PARTITION FUNCTION my_part_func (NUMERIC(7)) AS RANGE LEFT FOR VALUES (1,100,1000);
by: acosgaya | last post by:
hi, I am working in this problem, where I have a set of N d-dimensional points, e.g. (4,5,6,8) (2,0,4,6), are 4-d points, which I have stored in a vector of vectors. I am trying to partition the vector of vectors according to the median value of one the dimensions. I tried to use the stl partition method like this: // S is a vector of vectors containing the points vector < vector <int> >::iterator iter;
by: eavery | last post by:
I can't seem to find a way to do the following: create table part_table ( col1 int, col2 datetime ) on psX (datename(week,col2)) I want to partition based on the week number of a date field. So if I enter in data like the following in my part_table:
by: eavery | last post by:
Does anyone know of any documentation on the performance of partition merge/split? Does the merge or split of a partition cause any locking on the partitioned table? If you were merging or splitting a large volume of data rebalancing your partitioned table would you potentially lock users out?
by: Vinod Sadanandan | last post by:
Table Partition Performance analysis ============================================ Collection of Statistics for Cost-Based Optimization/DBMS_STATS vs. ANALYZE The cost-based approach relies on statistics and if the cost-based Approach is used , then statistics should be gernerated for all tables, clusters, and all types of indexes accessed by SQL statements. If the size and data distribution of your tables change frequently, then...
by: debasisdas | last post by:
SAMPLE CODE TO CREATE SUB PARTITIONS ======================================= RANGE-HASH-9i ------------------------- CREATE TABLE SUBPART ( ID NUMBER(10) PRIMARY KEY, NAME VARCHAR2(20) )
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.