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 FROM SaleState 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 FROM SaleState GROUP BY Bucket
ROUND
, but the set of buckets is different:
FLOOR(900/1000) = 0
ROUND(900,-3) = 1000
DEFINE ValueRange AS SELECT COUNT(1) AS CT FROM SaleState 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