* How do you fill the gaps in the ooutput from bucket_width


SQL> select * from table1;

        ID        AGE
---------- ----------
         1         19
         2         20
         3         22
         4         25
         5         18
         6         59
         7         43
         8         49
         9         52
        10         29
        11         19

        ID        AGE
---------- ----------
        12         62

12 rows selected.

-- Note that the grp 2 is not listed
SQL> select count(1), grp from (select width_bucket(age,20,60,4) grp from table1) group by grp;

  COUNT(1)        GRP
---------- ----------
         3          0
         4          1
         2          3
         2          4
         1          5

-- "Union all" 2 tables, one has all the bucket number of each row, one has ronum
-- excluding those over the number of buckets (4+2)
SQL> with t1 as (select id, age, WIDTH_BUCKET(age,20,60,4) grp, rownum rn from table1)
  2  select sum(cnt),grp from (select grp, 1 cnt from t1 union all (select rn as grp, 0 cnt from t1
  3  where rn < 6)) group by grp;

  SUM(CNT)        GRP
---------- ----------
         3          0
         4          1
         0          2
         2          3
         2          4
         1          5

6 rows selected.

SQL>