How to GROUP over a large range of numerical values?

[VolkerBarth]

An issue that came up in this answer:

Say, I have a large range of numerical values (doubles in this case), say from 0.0 - 50,000.0, in a column x. They are not evenly distributed in this range.

Now I want to build partitions to group them. Obviously a GROUP BY x is useless as it will typically group for each and every value. So I will have to define some intervals to group over - in my case, these intervals can be equally wide.

So I guess I will have to build categories by rounding x to them, say by

-- group by interval of ]x - 50, x + 50] 
select round(x / 100.0, 0) * 100.0 as xRounded, count(*)
from MyTable
group by xRounded
order by xRounded

This will group by intervals of 100 integers, each.

In order to sum up extrem intervals, I could use a further case expression, say

select case when xRounded <= 1000.0 then 1000.0
            when xRounded >= 20000.0 then 20000.0
            else xRounded end as xRounded2, count(*)
from (select round(x / 100.0, 0) * 100.0 as xRounded from MyTable) S
group by xRounded2
order by xRounded2

Is there a better way to do so?

[Breck_Carter]

select round ( x, -2 ) etcetera

[VolkerBarth]

Oh yes, indeed, that would be easier (and I forget the 2nd round parameter) - in my real sample, I've build groups by 200 or 500, so I need the addional division/multiplication, methinks...

[Former Member · Accepted solution]

I'm sure there are better ways to do this but you could create a join with a table for your partitions. The below shows an example for ranges of 100 from 0 to 5000 using the sa_rowgenerator to create the partitions (and generate test data).

BEGIN

//create data
SELECT RAND()*5000 as myVal INTO #myTemp FROM sa_rowgenerator(0,3000);

//sort data to view distribution
SELECT srg.row_num as lower_bound, srg.row_num + 100 as upper_bound, count(myVal) as cnt
FROM sa_rowgenerator(0, 4900, 100) srg, #myTemp mt
WHERE mt.myVal >= lower_bound AND mt.myVal < upper_bound
GROUP BY lower_bound
ORDER BY lower_bound;

END
go

[VolkerBarth · Accepted solution]

Thanks, the join with sa_rowgenerator seems rather smart - and somewhat better readable than the "rounding" approach, particularly when not using multiples of 10 as intervals. Performance is not really a concern so I guess that will work.