How to create range table based on table values

Question

I have this table

TableA

ID  Value
1       125  
2   400  
3   99  
4   130  
5   300  
6   350  
7   399  
..  
..

I want below table as a Output where range off set (100) is pre defined. Range value 100= TableA values between 0-100, 200 means 101-200

ResultTable

Range   Count  
100 1  
200 2  
300 1  
400 3  
..    
..

What is the best way to do it any idea suggestions.

Your second table has a count of 1 for 300, but TableA has 300 and 350. Shouldn't the count be 2? Or am I misunderstanding what you're asking? — Joe
300 means values between 201 to 300 so it would only include 300, 350 will come in range 301 to 400. — user781700

a1ex07 a1ex07 · Accepted Answer · 2013-10-10T16:25:06

Depends on RDMS you are using, the syntax will be a bit different (example is for Oracle), but general idea is

CREATE TABLE new_table AS 
SELECT CAST(value/100 as INT)*100 as range, count(*) as cnt
GROUP BY CAST(value/100 as INT)*100
FROM old_table;