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.
/** * 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
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