Partition Function Example 2

This example creates a select query that inspects the Order Amount field in an Orders table.  For each customer, it calculates the number of orders falling within each of several ranges.  The ranges are defined by the arguments to Partition:

start = 1, stop = 20000, interval = 1000.

You can use the following SQL statement for this example:

SELECT Orders.[Customer ID],
Partition(Int([Order Amount]), 1, 20000, 1000) AS Range,
Count(Orders.[Order ID]) AS [Count]
FROM Orders

GROUP BY Orders.[Customer ID],
Partition(Int([Order Amount]), 1, 20000, 1000);

The result of the select query could look like the following table.

Customer ID    Range                   Count

ALWAO           1: 1000      7

ANDRC           1: 1000      4

ANTHB           1: 1000      10

ANTHB       1001: 2000      2

ANTHB       2001: 3000      1

AROUT           1: 1000      9

AROUT       1001: 2000      3

AROUT       2001: 3000      1

AROUT       4001: 5000      1

BABUJ           1: 1000      10

BABUJ       1001: 2000      7

BABUJ       2001: 3000      2

BERGS           1: 1000      8

:           :    :

In this table, customer AROUT has 14 orders.  Nine are valued between 1 and 1000, three are valued between 1001 and 2000, and so on.