Grouping by range buckets

To create value range buckets, divide the records by the bucket size, and then use FLOOR or CEIL if needed to round to the nearest integer.

The following examples group sales into buckets by amount:

/**
  * This groups results into buckets by amount,
  * rounded to the nearest 1000.
  */
RETURN Results AS
SELECT
   ROUND(FactSales_SalesAmount, -3) AS Bucket,
   COUNT(1) AS CT
GROUP BY Bucket

/**
  * This groups results into buckets by amount,
  * truncated to the next-lower 1000.
  */
RETURN Results AS
SELECT
   FLOOR(FactSales_SalesAmount/1000)*1000 AS Bucket,
   COUNT(1) AS CT
GROUP BY Bucket

A similar effect can be achieved with ROUND, but the set of buckets is different:

FLOOR(900/1000) = 0
ROUND(900,-3) = 1000

In the following example, records are grouped into a fixed number of buckets:

DEFINE ValueRange AS SELECT
   COUNT(1) AS CT
GROUP BY SalesAmount
HAVING SalesAmount > 1.0
   AND SalesAmount < 10000.0;

RETURN Buckets AS SELECT
   SUM(CT) AS CT,
   FLOOR((SalesAmount - 1)/999.0) AS Bucket

FROM ValueRange
GROUP BY Bucket
ORDER BY Bucket