creating buckets in oracle sql
You were getting close.  WIDTH_BUCKET does not allow a subset of the ranges to be specified, you must specify the start and end of the whole range and how many buckets you want that range divided into.  In the case of 1-100, 101-200, 201-300, 301-400, &amp; 401-500 your start and end are 1 and 500 and this should be divided into five buckets.  This can be done as follows:

SELECT WIDTH_BUCKET (mycount, 1, 500, 5) Bucket FROM name_dupe;


Having the buckets we just need to count how many hits we have for each bucket using a group by.  This comes together with the above as follows:

SELECT Bucket*100 - 99 "Start", Bucket*100 "End", Count(Bucket) "Duplicates In Range" 
FROM
(
   SELECT WIDTH_BUCKET (mycount, 1, 500, 5) Bucket FROM name_dupe
)
GROUP BY Bucket ORDER BY Bucket;


There are some tradeoffs for creating the name_dupe table.  To avoid them you could combine your first query with this one as follows:

SELECT Bucket*100 - 99 "Start", Bucket*100 "End", Count(Bucket) "Duplicates In Range" 
FROM
(
    SELECT WIDTH_BUCKET (count(name), 1, 500, 5) Bucket FROM mynametable 
    GROUP BY name 
    HAVING count(name) &gt; 1
)
GROUP BY Bucket ORDER BY Bucket;


Finally, here is another solution avoiding the use of WIDTH_BUCKET, which was apparently added in 9.2.

SELECT rownum*100 - 99 "Start", rownum*100 "End", d2 "Duplicates In Range" FROM
(   
SELECT trunc((dupes+99)/100), count(trunc((dupes+99)/100)) d2 FROM 
   (
      SELECT name, count(name) dupes FROM mynametable GROUP BY name 
      HAVING count(name) &gt; 1 ORDER BY 2
   )
GROUP BY trunc((dupes+99)/100) ORDER BY 1
);


Test Data:

--drop table mynametable;
create table mynametable as (
   select 'a' name from dual connect by level &lt;= 1
   UNION ALL
   select 'b' name from dual connect by level &lt;= 100
   UNION ALL
   select 'c' name from dual connect by level &lt;= 101
   UNION ALL
   select 'd' name from dual connect by level &lt;= 120
   UNION ALL
   select 'e' name from dual connect by level &lt;= 200
   UNION ALL
   select 'f' name from dual connect by level &lt;= 250
   UNION ALL
   select 'g' name from dual connect by level &lt;= 300
   UNION ALL
   select 'h' name from dual connect by level &lt;= 400
);

create table name_dupe as (
   select name,count(*) mycount from mynametable
   group by name having count(*) &gt; 1
   );