Sometimes I like to fiddle around with TSQL. Not sure how useful this trick might be, but here is a code I came up with, that delivers histograms based on your data and a few variables you define. So as you can see, the dataset for this script is quite known Adventure Works DW dbo.factInternetSales . You can define the bucket count and the Bar chart width variables to fine tune your output. This histogram splits the dataset into the declared buckets based on the ProductKey FK column values.
SET NOCOUNT ON;
USE AdventureWorksDW2014;
DECLARE @BucketCount DECIMAL(8,3) = 10;
DECLARE @BarChartWidth INT = 100;
DECLARE @iKeyCount DECIMAL(8,3) = (SELECT COUNT(DISTINCT ProductKey) FROM FactInternetSales);
DECLARE @iBucketSize DECIMAL(8,3) = @iKeyCount / @BucketCount;
SELECT
i.*,
REPLICATE('=', @BarChartWidth * i.SumSalesAmount / (SELECT SUM(SalesAmount) FROM factInternetSales)) AS [BarChart]
FROM (
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) Bucket_ID,
MIN(ii.ProductKey) Bucket_Range_From,
MAX(ii.ProductKey) Bucket_Range_To,
COUNT(ii.Bucket_ProductKeys_Count) Bucket_ProductKeys_Count,
SUM(ii.SumSalesAmount) SumSalesAmount
FROM (
SELECT
ProductKey,
CEILING(CAST((ROW_NUMBER() OVER (ORDER BY ProductKey) )/@iBucketSize AS DECIMAL(8,3))) iBucket_ID,
COUNT(*) Bucket_ProductKeys_Count,
SUM(SalesAmount) SumSalesAmount
FROM FactInternetSales
GROUP BY ProductKey
) ii
GROUP BY ii.iBucket_ID
) i;
And the result may look like this:
But I would also like to see more accurate solution. So digging deeper, I came up with a code, that splits the dataset into the declared buckets based on the composite PK SalesOrderNumber, SalesOrderLineNumber. ( I also added precision to the decimal Datatype ) This code still feels quite straightforward to me, but gets a little bit more complex.
SET NOCOUNT ON;
USE AdventureWorksDW2014;
DECLARE @BarChartWidth INT = 100;
DECLARE @BucketCount DECIMAL(38,18) = 10;
DECLARE @iCount DECIMAL(38,18) = (SELECT COUNT(*) FROM FactInternetSales);
DECLARE @iBucketSize DECIMAL(38,18) = @iCount / @BucketCount;
;WITH CTE AS
(
SELECT
i.iKey,
i.iID,
i.iBucket_ID,
i.SumSalesAmount
FROM (
SELECT
TOP 100 PERCENT
CAST([SalesOrderNumber] AS VARCHAR) + '-' + CAST([SalesOrderLineNumber] AS VARCHAR) iKey,
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) iID,
CEILING(CAST((ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) ) / @iBucketSize AS DECIMAL(38,18))) iBucket_ID,
[SalesOrderNumber],
[SalesOrderLineNumber],
SUM(SalesAmount) SumSalesAmount
FROM FactInternetSales
GROUP BY [SalesOrderNumber],[SalesOrderLineNumber]
ORDER BY CAST(REPLACE([SalesOrderNumber],'SO','') AS INT),[SalesOrderLineNumber]
) i
)
SELECT
i.iBucket_ID,
i.Bucket_Range_From,
i.Bucket_Range_To,
(SELECT Cfrom.iKey FROM CTE Cfrom WHERE Cfrom.iID = i.Bucket_Range_From) Bucket_Range_SalesOrderKey_From,
(SELECT Cto.iKey FROM CTE Cto WHERE Cto.iID = i.Bucket_Range_To) Bucket_Range_SalesOrderKey_To,
i.Bucket_Row_Count,
i.Bucket_Sales_Amount,
REPLICATE('=', @BarChartWidth * i.Bucket_Sales_Amount / (SELECT SUM(SalesAmount) FROM factInternetSales)) AS [BarChart]
FROM (
SELECT
TOP 100 PERCENT
ii.iBucket_ID,
MIN(ii.iID) Bucket_Range_From,
MAX(ii.iID) Bucket_Range_To,
COUNT(ii.iID) Bucket_Row_Count,
SUM(ii.SumSalesAmount) Bucket_Sales_Amount
FROM
CTE ii
GROUP BY ii.iBucket_ID
ORDER BY ii.iBucket_ID
) i
ORDER BY iBucket_ID;
The final result is as expected ( check the Bucket_Row_Count column ) and can look like this:
