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: - Partition(number, start, stop, interval)
- number - whole number to be evaluated against the ranges.
- start - whole number that is the start of the overall range of number.
- stop - whole number that is the end of the overall range of numbers.
- interval - difference between range elements.
- 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:
-
[Age] [Testrslt] (Fields in tblTest)
-
-
73 Satisfactory
-
19 Satisfactory
-
19 Unsatisfactory
-
20 Satisfactory
-
24 Unsatisfactory
-
21 Satisfactory
-
16 Satisfactory
-
45 Unsatisfactory
-
50 Satisfactory
-
55 Unsatisfactory
-
75 Satisfactory
-
80 Unsatisfactory
-
62 Satisfactory
-
64 Unsatisfactory
-
46 Unsatisfactory
-
47 Unsatisfactory
-
49 Unsatisfactory
-
48 Unsatisfactory
-
56 Unsatisfactory
-
58 Satisfactory
-
72 Satisfactory
-
67 Unsatisfactory
-
15 Satisfactory
-
17 Satisfactory
-
63 Unsatisfactory
-
66 Unsatisfactory
-
61 Unsatisfactory
-
33 Satisfactory
-
50 Satisfactory
-
51 Satisfactory
-
54 Satisfactory
-
32 Unsatisfactory
- SELECT DISTINCTROW [Testrslt], Partition([Age],0,100,5) AS Age_Range, Count([Age]) AS Range_Count
-
FROM tblTest
-
GROUP BY [Testrslt], Partition([Age],0,100,5);
-
[Testrslt] [Age_Range] [Range_Count]
-
Satisfactory 15: 19 4
-
Satisfactory 20: 24 2
-
Satisfactory 30: 34 1
-
Satisfactory 50: 54 4
-
Satisfactory 55: 59 1
-
Satisfactory 60: 64 1
-
Satisfactory 70: 74 2
-
Satisfactory 75: 79 1
-
Unsatisfactory 15: 19 1
-
Unsatisfactory 20: 24 1
-
Unsatisfactory 30: 34 1
-
Unsatisfactory 45: 49 5
-
Unsatisfactory 55: 59 2
-
Unsatisfactory 60: 64 3
-
Unsatisfactory 65: 69 2
-
Unsatisfactory 80: 84 1
8 17280
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 :-)
Nic;o).
Although I agree with Nico I appreciate learning a new function. Never know when it might come in useful. :D
Thanks for Posting this informational Link.
Does this work in Access? Isn't DistinctRow SQL-only?
The Partition() Function will work in Access.
Not sure if DISTINCTROW does anything Jim but it doesn't throw an error.
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...
|
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...
|
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...
|
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...
|
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...
|
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....
|
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...
|
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...
|
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: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
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...
|
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,...
|
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...
|
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...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
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...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |