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 "Count" 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 "Count" GROUP BY Bucket
In the following example, records are grouped into a fixed number of buckets:
DEFINE ValueRange AS SELECT COUNT(1) AS "Count" GROUP BY SalesAmount HAVING SalesAmount > 1.0 AND SalesAmount < 10000.0; RETURN Buckets AS SELECT SUM("Count") AS "Count", FLOOR((SalesAmount - 1)/999.0) AS Bucket FROM ValueRange GROUP BY Bucket ORDER BY Bucket