Partition Function Example 1

This example creates a select query that inspects the Order Amount field in an Orders table.  It uses the Partition and the SQL Count functions to evaluate and count the number of orders in each of the 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 DISTINCTROW Partition([Order Amount], 1, 20000, 1000) AS Range,
  Count(Orders.[Order Amount]) AS [Count] FROM Orders
  GROUP BY Partition([Order Amount], 1, 20000, 1000)
  ORDER BY Partition([Order Amount], 1, 20000, 1000),
  Count(Orders.[Order Amount]);

 

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

Range                  Count

 

    1: 1000   588

 1001: 2000   274

 2001: 3000   111

 3001: 4000   38

 4000: 5000   33

 5001: 6000   6

 6001: 7000   8

 7001: 8000   2

 8001: 9000   4

 9001:10000   3

10001:11000   6

11001:12000   2

12001:13000   1

13001:14000   1

16001:17000   1

 

This table contains no orders for the ranges 14001:15000 and 15001:16000.