It looks ok to me, apart from UNION picking up the 40th bin. When this is done, the query is quite efficient:

;with cteHistogram (N, StartBin, EndBin, TotalCount)

as

(

select

N,

@min-2+(@interval*(N-1)) StartBin,

@min-2+(@interval*N) EndBin, coalesce(count(RandomFloat), 0) TotalCount

from @MyTestTable

cross join Tally

where RandomFloat >= @min-2+(@interval*(N-1))

and ((RandomFloat < @min-2+(@interval*N) and n <= @bins-1)

OR (RandomFloat <= @max-2 and n = @bins))

group by N

)

select

Tally.N, @min-2+(@interval*(Tally.N-1)) StartBin, case when Tally.N = @bins then @max-2 else @min-2+(@interval*Tally.N) end EndBin, coalesce(TotalCount, 0) TotalCount

from

Tally left outer join cteHistogram on Tally.N = cteHistogram.N

where

Tally.N <= @bins

order by

Tally.N

Note that min and max are the calculated range min and max values, rather than RandomFloat values. If you want RandomFloat values, try this:

-------------------------------------------------------------------------

-- calculate the range start and end for each bin

;WITH Bins AS (

SELECT

t.n,

RangeStart = minval + (interval * (t.n-1)),

RangeEnd = CASE WHEN t.n = @bins THEN maxval+1 ELSE minval + (interval * t.n) END

FROM (

SELECT

interval = (MAX(RandomFloat) - MIN(RandomFloat)) / @bins,

minval = MIN(RandomFloat),

maxval = MAX(RandomFloat)

FROM @MyTestTable

) d

CROSS JOIN Tally t

WHERE t.n <= @bins

)

-- Summarise the bin contents. Min and max are actual bins.

SELECT

b.N,

StartBin = MIN(RandomFloat),

EndBin = MAX(RandomFloat),

RangeStart = MIN(b.RangeStart),

RangeEnd = MIN(b.RangeEnd),

TotalCount = COUNT(*)

FROM Bins b

INNER JOIN @MyTestTable t

ON t.RandomFloat >= b.RangeStart AND t.RandomFloat < b.RangeEnd

GROUP BY N

Now, if your requirement was to split the population into 40 equal groups and summarise those groups, you could use NTILE, like this:

-- split the set into (@bins) roughly equal-sized populations

SELECT

N,

StartBin = MIN(RandomFloat),

EndBin = MAX(RandomFloat),

TotalCount = COUNT(*)

FROM (

SELECT

RandomFloat,

N = NTILE(@bins) OVER(ORDER BY (RandomFloat))

FROM @MyTestTable

) d

GROUP BY N