Oracle hierarchical sql with rollup count

How would I write SQL in Oracle to return tree view with rolloup count:

SQL would return this:

KLAS - COUNT

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

ROOT - 10

   KLAS1 - 5

       KLAS2 - 2

       KLAS3 - 3

  KLAS4 - 5

       KLAS5 - 5

  KLAS6 - 0

       KLAS7 - 0

I have two tables, one is where is structure hold, second is where I have data. Both tables are joined by klas coumn

Code for reproducing tables:

create table table1 (id number, parent_id number, klas varchar2(10));  
insert into table1 (id, parent_id, klas) values (1, null, 'ROOT');  
insert into table1 (id, parent_id, klas) values (2, 1, 'KLAS1');  
insert into table1 (id, parent_id, klas) values (3, 2, 'KLAS2');  
insert into table1 (id, parent_id, klas) values (4, 2, 'KLAS3');  
insert into table1 (id, parent_id, klas) values (5, 1, 'KLAS4');  
insert into table1 (id, parent_id, klas) values (6, 5, 'KLAS5');  
insert into table1 (id, parent_id, klas) values (7, 1, 'KLAS6');  
insert into table1 (id, parent_id, klas) values (8, 7, 'KLAS7');  



create table table2 (klas varchar2(10), cnt number);  
insert into table2(klas, cnt) values ('KLAS2', 2);  
insert into table2(klas, cnt) values ('KLAS3', 3);  
insert into table2(klas, cnt) values ('KLAS5', 5);  

commit;  

Regards, Igor


Source: oracle

Leave a Reply